Skip site navigation (1) Skip section navigation (2)

AutoIncrement not working on this table only

From: "Ben Kassel" <benk(at)ix(dot)netcom(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Cc: <kasselb(at)nswccd(dot)navy(dot)mil>
Subject: AutoIncrement not working on this table only
Date: 2002-11-03 16:21:35
Message-ID: 000d01c28355$166a76d0$de0c2e82@codeine (view raw or flat)
Thread:
Lists: pgsql-novice
MY WORK MAIL SERVER IS DOING STRANGE THINGS TODAY. IF YOU WANT TO RESPOND DIRECTLY TO ME PLEASE RESPOND TO THE kasselb(at)nswccd(dot)navy(dot)mil as refelcted in the cc line.

thanks,
ben

When I try to create a new row in this table and do not explicitly define a unique value for datadefindex I get
 the following error message:

 ERROR:  Cannot insert a duplicate key into unique index datadef_pkey 

 Here is the INSERT statement that generated the error: 

 tmdb=# insert into datadef (cfgmgmtid, datadefname, datadefformat, datadefunits, datadefdescription)
 VALUES (2, 'TestValue', 'REAL', 'N/A', 'This is a test of placing a new row without an explicit datadefindex');

 Here is the table definition: 

 tmdb=# \d datadef 
                                              Table "datadef" 
        Column       |         Type          |                         Modifiers 

 --------------------+-----------------------+----------------------------------- 
 ------------------------- 
  datadefindex       | integer               | not null default nextval('datadef_ 
 datadefindex_seq'::text) 
  cfgmgmtid          | integer               | 
  datadefname        | character varying(80) | not null 
  datadefformat      | character varying(80) | not null 
  datadefunits       | character varying(80) | not null 
  datadefdescription | text                  | not null 
 Primary key: datadef_pkey 
 Unique keys: datadefname_idx 
 Triggers: RI_ConstraintTrigger_19507, 
           RI_ConstraintTrigger_19509, 
           RI_ConstraintTrigger_19511, 
           RI_ConstraintTrigger_19513, 
           RI_ConstraintTrigger_19515, 
           RI_ConstraintTrigger_19659, 
           RI_ConstraintTrigger_19661, 
           RI_ConstraintTrigger_19663, 
           RI_ConstraintTrigger_19665, 
           RI_ConstraintTrigger_19667 

 And finally here is the entry in the datadef_datadefindex_seq table: 

 tmdb=# select * from datadef_datadefindex_seq; 
       sequence_name       | last_value | increment_by | max_value  | min_value | 
  cache_value | log_cnt | is_cycled | is_called 
 --------------------------+------------+--------------+------------+-----------+ 
 -------------+---------+-----------+----------- 
  datadef_datadefindex_seq |          8 |            1 | 2147483647 |         1 | 
            1 |      32 | f         | t 
 (1 row) 

 Notice that last_value = 8, owever the current number of rows in the datadef table = 67. 

 My current workaround is to do a MAX(datadefindex) on datadef, increment it by one and explicitly place that
 value as the datradefindex for the new row, however I am worried about the database stability.

 More information : If I DROP the database, recreate it, and enter values into the table manually, the
 autoincrement works on this table. It seems that the problem arises after I reload the data into the table using
 the \i command on a file which was created using the pg_dump command.

 I have recently upgraded from 7.2.1 to 7.2.3 using the RPM. 

 Thanks in advance, 

 ben 

pgsql-novice by date

Next:From: Nick SayerDate: 2002-11-03 16:50:06
Subject: Novice schema question
Previous:From: Reshat SabiqDate: 2002-11-03 08:38:27
Subject: moving a postgreSQL DB, etc.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group