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

Arcady,

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

Perform the insert, if successful then update the row:
UPDATE a
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.

Ciao
--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)
>