One way to get into a situation like that is loading the table data with COPY (the input contains the pks, and the COPY command does not update the sequence, you have to do that manually after the copy is done).
Another way is simply inserting a row with an explicitly specified pkey:
insert into fdata (fid,...) values (100, ...);
Now, assuming, that you current sequence value is less then 100, and that the statement above succeedes (i.e., there is no fid=100 in the table yet), you'll get your sequence out of date. You'll still be able to use it, and insert the rows into the table *until* the current value reaches 100 - once that happens, an attempt to insert with the default fid will cause an error, because the sequence will generate a key, that already exists.
To fix this, you need to do something like:
select setval ('fdata_fid_seq', (select fid from fdata order by fid limit 1));
This will make sure that the next value your sequence generates is greater than any key that already exists in the table.
I hope, it helps...
Dima
insert into fdata
Scott Cain wrote:
Hello,
I sent this question yesterday morning, but it was not allowed because I wasn't subscribed to the list. If it did make it through, I appologize for the dup.
I am having strange behavior when I try to do an insert. Postgres tells me that it cannot insert a duplicate key into a primary key index, when I am not trying to insert into it. Can anyone shed light on why this is happening, or point out the error of my ways?
Here are the details:
wormbase=> \d fdata
Table "public.fdata"
Column | Type | Modifiers ---------------+------------------------+--------------------------------------- -----------------
fid | integer | not null default nextval('public.fdata _fid_seq'::text)
fref | character varying(100) | not null default ''
fstart | integer | not null default '0'
fstop | integer | not null default '0'
fbin | double precision | not null default '0.000000'
ftypeid | integer | not null default '0'
fscore | double precision |
fstrand | character varying(3) |
fphase | character varying(3) |
gid | integer | not null default '0'
ftarget_start | integer |
ftarget_stop | integer |
Indexes: pk_fdata primary key btree (fid),
fdata_fref_idx btree (fref, fbin, fstart, fstop, ftypeid, gid),
fdata_ftypeid_idx btree (ftypeid),
fdata_gid_idx btree (gid)
Check constraints: "chk_fdata_fstrand" ((fstrand = '+'::character varying) OR (f strand = '-'::character varying))
"chk_fdata_fphase" (((fphase = '0'::character varying) OR (fp hase = '1'::character varying)) OR (fphase = '2'::character varying))
Now a chunk from my query log: Jul 14 12:48:47 localhost postgres[2998]: [107-1] LOG: query: INSERT INTO fdata (fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftarget_start,ftarget_stop) Jul 14 12:48:47 localhost postgres[2998]: [107-2] VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'12358',NULL,NULL) Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR: Cannot insert a duplicate key into unique index pk_fdata
Note that I do not try to insert anything into fid, the primary key on this table. Why does Postgres think I am?
Thanks much, Scott
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
