When I change an id (primary key serial) in a table, the next value
returned by the sequence for the id can conflict with that id (e.g.,
change the id to be id + 1). MySQL seems to handle this transparently
by skipping conflicting values, but with PostgreSQL I get primary key
conflicts. It se
Tom Lane wrote:
Steven Brown <[EMAIL PROTECTED]> writes:
When I change an id (primary key serial) in a table, the next value
returned by the sequence for the id can conflict with that id (e.g.,
change the id to be id + 1).
[...]
Plan A: don't do that. Why in the world is it a g
I want to allow access to a table's rows without allowing that table to
be damaged. A problem I have is with my serial primary key 'id' field.
Although I can block its UPDATE, if users INSERT with an explicit 'id'
higher than the sequence, future INSERTs will fail due to the values
colliding.
Martijn van Oosterhout wrote:
IIRC you can set the permissions on a sequence to allow nextval but not
setval.
I've not been able to find a way - granting UPDATE grants the use of
both. Someone in the interactive docs ran into the same thing it seems:
http://www.postgresql.org/docs/8.0/inte
Csaba Nagy wrote:
That way they really can't touch the sequence; otherwise they still
could call nextval multiple times erroneously (people do that...). It
doesn't matter much to the sequence, of course... It just leaves the
ugly gaps out :P
The sequence facility was NOT designed with no-gap
Tino Wildenhain wrote:
since your insert above would call nextval() per default,
its save to use currval() in the same transaction.
Ah, I didn't realize currval() was handled session-local - that removes
my need to support any non-default value to my serial column. So, if I
can identify use
Tom Lane wrote:
[...]
I think the solution for you is to use BEFORE triggers as suggested
upthread. The BEFORE INSERT trigger function should be SECURITY DEFINER
and owned by a user who has permission to NEXTVAL the sequence. The id
column should probably be declared plain integer (or bigint),
