From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | x asasaxax <xanaruto(at)gmail(dot)com>, PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Primary Key with serial the solution? |
Date: | 2008-04-02 03:12:23 |
Message-ID: | 47F2F997.30105@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2008-04-02 03:49:47 | Re: Primary Key with auto increment field |
Previous Message | Bruce Momjian | 2008-04-02 03:06:26 | Re: [GENERAL] SHA1 on postgres 8.3 |