pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with Auto Increment
2+ messages / 2 participants
[nested] [flat]
* Problem with Auto Increment
@ 2002-11-04 18:29 Ben Kassel <kasselb@nswccd.navy.mil>
2002-11-04 20:45 ` Re: Problem with Auto Increment Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Ben Kassel @ 2002-11-04 18:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Problem with Auto Increment
2002-11-04 18:29 Problem with Auto Increment Ben Kassel <kasselb@nswccd.navy.mil>
@ 2002-11-04 20:45 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2002-11-04 20:45 UTC (permalink / raw)
To: Ben Kassel <kasselb@nswccd.navy.mil>; +Cc: pgsql-sql
On Mon, 4 Nov 2002, Ben Kassel wrote:
> 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:
> 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.
Was that a data only dump that you were reloading? I'd guess that a data
only dump is assuming that you have/will set the sequence's next value
correctly yourself. You might just try finding the max value and using
setval on the sequence to get the sequence in the right place.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-11-04 20:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-04 18:29 Problem with Auto Increment Ben Kassel <kasselb@nswccd.navy.mil>
2002-11-04 20:45 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox