Re: Primary Key with serial the solution?
How about if i do this inside a procedure:
SELECT setval('sequence',(SELECT max(id) FROM table)) INTO variable;
insert into table values(variable, ..., ...); ?
Will this be transactional? Cause, they say that setval is a command
that its transactional. Using this way i
don´t will need to use a sequence anymore. Is that correct?
Thanks you all.
2008/3/31, x asasaxax <xanaruto@gmail.com>:
Show quoted text
can anyone do a example for me.. an explain how it works?
Thanks a lot
2008/3/29, Berend Tober <btober@ct.metrocast.net>:
x asasaxax wrote:
I have the following table create table product(cod serial,
user_cod
bigint, constraint product_fk Foreign Key(user_cod) references
user(cod),
constraint product_pk Primary Key(cod, user_cod));
What i want to happend is that:
user_cod cod
1 1
1 2
1 3
2 1
3 1
3 2Can serial do that? ...
No.
...what can i do to make this happen?
http://archives.postgresql.org/pgsql-general/2006-08/msg00744.php
x asasaxax wrote:
How about if i do this inside a procedure:
SELECT setval('sequence',(SELECT max(id) FROM table)) INTO variable;
insert into table values(variable, ..., ...); ?Will this be transactional? Cause, they say that setval is a command
that its transactional. Using this way i
don�t will need to use a sequence anymore. Is that correct?
If somebody else INSERTs a record between your first and second
statements, it will get the first free value in the sequence so your
INSERT will fail with a unique check voliation. Assuming there's a
unique constraint involved, which I assume there is given your use of a
sequence.
Why do you want to do this? Sequences are designed so that you can just:
INSERT INTO table VALUES ( nextval('sequence'), ..., ...)
or set the DEFAULT on the generated value field such that it calls
nextval('sequence') if the user just does:
INSERT INTO table VALUES ( DEFAULT, ... , ... )
or uses a named-field INSERT and omits the sequence column entirely.
Why not use them that way?
Is there something you're trying to achieve that sequences aren't doing
the job for - like, say, "gap-less" generated values? If that's the
problem please search the archives as it's already been discussed to
death even in the short time I've been a list member.
--
Craig Ringer