AutoIncrement not working on this table only - Mailing list pgsql-novice
| From | Ben Kassel |
|---|---|
| Subject | AutoIncrement not working on this table only |
| Date | |
| Msg-id | 000d01c28355$166a76d0$de0c2e82@codeine Whole thread Raw |
| List | pgsql-novice |
MY WORK MAIL SERVER IS DOING STRANGE THINGS TODAY. IF YOU WANT TO RESPOND DIRECTLY TO ME PLEASE RESPOND TO THE kasselb@nswccd.navy.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:
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');
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
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
-------------------------
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)
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.
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.
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: