Re: Enforcing serial uniqueness?

Steven Brown schrieb:
> Martijn van Oosterhout wrote:
>
...
>> Secondly, if you don't want people to be able to stuff with your ID
>> column, you could set a BEFORE INSERT trigger to overwrite whatever
>> they provide and a BEFORE UPDATE trigger to cancel any changes...
>
>
> The problem is that to get the last inserted id, as far as I know, you
> need to select a value ahead of time via nextval, then insert with it.
> If I simply block/change all forced entries, I'll wind up blocking that,
> and won't know what id the row I just inserted got.

Just turn it around (which has the advantage you dont have to
transfer to and fro the database in 2 steps):

INSERT INTO thetable (col1,col2,col3) VALUES ( ... );
SELECT currval('thetable_id_seq');

since your insert above would call nextval() per default,
its save to use currval() in the same transaction.

For sequences (instead of just an int column with default)
there is even a function to find the sequence for that
column.

> I believe there's a way to get the last row oid and use that to figure
> out what id was used, but I think that would require all clients to be
> PostgreSQL-specific, so isn't too useful if you don't control all the
> clients.

No, you dont mess around with oids.

Regards
Tino