AutoIncrement not working on this table only
От | Kassel Ben CRBE |
---|---|
Тема | AutoIncrement not working on this table only |
Дата | |
Msg-id | 1F9F67162ADED3119F18009027A8F404025FC39B@crbeex03.dt.navy.mil обсуждение исходный текст |
Список | pgsql-novice |
When I try to create a new row in this table and do not explicitly define a unique value for datadefindex I get the followingerror 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 thedatradefindex 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 thedata 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 по дате отправления: