From: Dmitry Tkach <dmitry@openratings.com>
To: Scott Cain <cain@cshl.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Cannot insert dup id in pk
Date: Tue, 15 Jul 2003 10:43:02 -0400
Message-ID: <3F1412F6.2020100@openratings.com> (raw)
In-Reply-To: <1058274025.1451.6.camel@localhost.localdomain>
References: <1058274025.1451.6.camel@localhost.localdomain>
You must have your sequence out of date with the content of the table
(so that the next value in the sequence has already been inserted).
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
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, cain@cshl.org
Subject: Re: Cannot insert dup id in pk
In-Reply-To: <3F1412F6.2020100@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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