Frequently Asked Questions (FAQ) for PostgreSQL

Last updated:Mon Oct8 23:19:46 EDT 2007

Last updated:Tue Oct9 15:52:10 EDT 2007

Current maintainer: Bruce Momjian (bruce@momjian.us)

name TEXT

);

See the create_sequence manual page for more information about

sequences.

Automatically created sequence are named <table>_<serialcolumn>_seq,

where table and serialcolumn are the names of the table and SERIAL

column, respectively. See the create_sequence manual page for more

information about sequences.

4.11.2) How do I get the value of a SERIAL insert?

One approach is to retrieve the next SERIAL value from the sequence

object with the nextval() function before inserting and then insert it

explicitly. Using the example table in 4.11.1, an example in a

pseudo-language would look like this:

new_id = execute("SELECT nextval('person_id_seq')");

execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");

You would then also have the new value stored in new_id for use in

other queries (e.g., as a foreign key to the person table). Note that

the name of the automatically created SEQUENCE object will be named

<table>_< serialcolumn>_seq, where table and serialcolumn are the

names of your table and your SERIAL column, respectively.

Alternatively, you could retrieve the assigned SERIAL value with the

currval() function after it was inserted by default, e.g.,

execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");

new_id = execute("SELECT currval('person_id_seq')");

The simplest way is to retrieve the assigned SERIAL value with

RETURNING. Using the example table in 4.11.1, it would look like this:

INSERT INTO person (name) VALUES ('Blaise Pascal') RETURNING id;

You can also call nextval() and use that value in the INSERT, or call

currval() after the INSERT.

4.11.3) Doesn't currval() lead to a race condition with other users?

No. currval() returns the current value assigned by your session, not

alink="#0000ff">

<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>

<P>Last updated:Mon Oct8 23:19:46 EDT 2007</P>

<P>Last updated:Tue Oct9 15:52:10 EDT 2007</P>

<P>Current maintainer: Bruce Momjian (<Ahref=

"mailto:bruce@momjian.us">bruce@momjian.us</A>)

);

See the<I>create_sequence</I> manual page for more information

about sequences.

<P>Automatically created sequence are named

&lt;<I>table</I>&gt;_&lt;<I>serialcolumn</I>&gt;_<I>seq</I>, where

<I>table</I> and<I>serialcolumn</I> are the names of the table and

<SMALL>SERIAL</SMALL> column, respectively. See the

<I>create_sequence</I> manual page for more information about

sequences.</P>

<H3id="item4.11.2">4.11.2) How do I get the value of a

<SMALL>SERIAL</SMALL> insert?</H3>

<P>One approach is to retrieve the next<SMALL>SERIAL</SMALL> value

from the sequence object with the<I>nextval()</I> function

<I>before</I> inserting and then insert it explicitly. Using the

example table in<Ahref="#item4.11.1">4.11.1</A>, an example in a

pseudo-language would look like this:</P>

new_id = execute("SELECT nextval('person_id_seq')");

execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");

<P>The simplest way is to retrieve the assigned<SMALL>SERIAL</SMALL>

value with<SMALL>RETURNING</SMALL>. Using the example table in<A

href="#item4.11.1">4.11.1</A>, it would look like this:</P>

You would then also have the new value stored in<CODE>new_id</CODE>

for use in other queries (e.g., as a foreign key to the<CODE>person

</CODE> table). Note that the name of the automatically created

<SMALL>SEQUENCE</SMALL> object will be named &lt;<I>table</I>&gt;_&lt;<I>

serialcolumn</I>&gt;_<I>seq</I>, where<I>table</I> and<I>serialcolumn</I>

are the names of your table and your<SMALL>SERIAL</SMALL> column,

respectively.

<P>Alternatively, you could retrieve the assigned<SMALL>SERIAL</SMALL>

value with the<I>currval()</I> function<I>after</I> it was inserted by

default, e.g.,</P>

execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");

new_id = execute("SELECT currval('person_id_seq')");

INSERT INTO person (name) VALUES ('Blaise Pascal') RETURNING id;

You can also call<I>nextval()</I> and use that value in the

<SMALL>INSERT</SMALL>, or call<I>currval()</I><I>after</I> the

<SMALL>INSERT</SMALL>.

<H3id="item4.11.3">4.11.3) Doesn't<I>currval()</I>

lead to a race condition with other users?</H3>