Skip site navigation (1) Skip section navigation (2)

Re: Not incrementing the 'serial' counter on failed inserts.

From: Louis Bertrand <louis(at)bertrandtech(dot)on(dot)ca>
To: Arcady Genkin <antipode(at)thpoon(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Not incrementing the 'serial' counter on failed inserts.
Date: 2001-03-13 13:40:02
Message-ID: Pine.BSO.4.20.0103130818470.792-100000@grendel.bts (view raw, whole thread or download thread mbox)
Lists: pgsql-novice

I'd make the id field just an integer and use a separate sequence:
 SELECT setval( 'idnumber', nnnn); /* nnnn is your starting number */

Perform the insert, if successful then update the row:
   SET serial = nextval('idnumber') 
   WHERE foo=$newfoo AND bar=$newbar; /* variables hold new values */

Look into bracketing both operations within a BEGIN-COMMIT block. Not sure
how that works with sequences, but it might keep that number from
incrementing if you do it the way you wrote.

 --Louis  <louis(at)bertrandtech(dot)on(dot)ca> 

On 10 Mar 2001, Arcady Genkin wrote:

> I have a (probably generic) problem: a table like
> create table a (
>        id serial,
>        foo text,
>        bar text,
>        unique( foo, bar ) );
> >From a PHP script, I do an INSERT and then check by the return value
> of pg_exec() function whether the insert failed because the entry
> already exists.  The problem is that the sequence on the 'id' field
> gets incremented even if the insert fails.
> What's the typical way of addressing this problem?
> I thought about doing a SELECT, and then INSERT only if the SELECT
> returns 0 rows.  But then there is a possibility that in between the
> SELECT and INSERT queries some other client will do an INSERT on the
> same values, and then my INSERT will fail (again, incrementing the
> 'id').
> Many thanks for any input,
> -- 
> Arcady Genkin
> Nostalgia isn't what it used to be.
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

In response to

pgsql-novice by date

Next:From: Brett W. McCoyDate: 2001-03-13 14:26:22
Subject: Re: Fwd: Silly question about numbering of rows?
Previous:From: Scott MuirDate: 2001-03-13 11:02:47
Subject: FW: look-up cache failures

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group