By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,780 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,780 IT Pros & Developers. It's quick & easy.

Retrieving tuple data on insert

P: n/a
Hello,

I have a table with two columns, created with : CREATE TABLE test ( id
serial primary key, name text ) ;

To populate the table i use :
INSERT INTO test(name) values('test1'); so the "id" is automatically set
by PostgreSQL.

Now the problem, i would like to retrieve the value of "id" that
PostgreSQL assigns to this tuple, but i can't make a SELECT on the name i
just inserted because it's not UNIQUE, so SELECT may return multiple
results.

Is there a way to retrieve the value of "id" from the insertion ?

I was thinking of something with Oids, like getting the Oid of the INSERT
statement then use it to SELECT the tuple, but i didn't find what to do
with an Oid in PostgreSQL documentation.

Also, it would be better if it was possible in only one operation (not
INSERT then SELECT).

Thanks in advance.
Nov 11 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
On Mon, 04 Aug 2003 03:44:32 +0200, sebmil wrote:
Hello,

I have a table with two columns, created with : CREATE TABLE test ( id
serial primary key, name text ) ;

To populate the table i use :
INSERT INTO test(name) values('test1'); so the "id" is automatically set
by PostgreSQL.


You can get this easily if you are still within the same transaction.

The Postgres 'serial' type is really a shorthand for creating a sequence,
and setting the default value of the field to the NEXTVAL() of the
sequence.

You can use the CURVAL() function to determine the current value of a
sequence, as long as you know the name of the sequence. Postgres follows a
simple naming convention for the serial numbers: tablename_fieldname_seq,
so in your case the sequence would be called test_id_seq:

select CURVAL('test_id_seq');

I don't know if having autocommit on prevents you from using transactions,
but at least you've got something to look into.

cf

Nov 11 '05 #2

P: n/a
On Mon, 04 Aug 2003 09:50:13 +0000, Colin Fox wrote:
On Mon, 04 Aug 2003 03:44:32 +0200, sebmil wrote:
Hello,

I have a table with two columns, created with : CREATE TABLE test ( id
serial primary key, name text ) ;

To populate the table i use :
INSERT INTO test(name) values('test1'); so the "id" is automatically set
by PostgreSQL.


You can get this easily if you are still within the same transaction.

The Postgres 'serial' type is really a shorthand for creating a sequence,
and setting the default value of the field to the NEXTVAL() of the
sequence.

You can use the CURVAL() function to determine the current value of a
sequence, as long as you know the name of the sequence. Postgres follows a
simple naming convention for the serial numbers: tablename_fieldname_seq,
so in your case the sequence would be called test_id_seq:

select CURVAL('test_id_seq');

I don't know if having autocommit on prevents you from using transactions,
but at least you've got something to look into.


Thanks for the help, but since the database is used by many clients
simultaneously, i can't be sure that the value i get from "select curval"
will be the one used in the next (or previous ?) insert made by this
client.

Also, i don't know what is "autocommit", but i will look further for
that. I'm just using postgreSQL's C interface like :
PGresult *res = PQexec(db_cnx, "insert into...");
Nov 11 '05 #3

P: n/a
On Mon, 04 Aug 2003 21:00:01 +0200, sebmil wrote:
Thanks for the help, but since the database is used by many clients
simultaneously, i can't be sure that the value i get from "select curval"
will be the one used in the next (or previous ?) insert made by this
client.
That's why I said that it had to be within the same transaction. It
doesn't matter if others are using the same sequence - within a single
transaction the CURVAL will always remain the same.
Also, i don't know what is "autocommit", but i will look further for
that. I'm just using postgreSQL's C interface like :
PGresult *res = PQexec(db_cnx, "insert into...");


Autocommit is a flag that tells Postgres to do a commit after every
insert/update (and possibly delete? I'd have to check that). The
administrator for the database has control over that. I don't have my docs
with me right now, but I think that an explicit begin/commit pair will
override autocommit.

Here's an example series of SQL that should do what you want:

begin;
insert into test(name) values ('something');
select CURVAL('test_id_seq');
commit;

At the end, you must either commit(save changes) or rollback(undo changes).

Another way to do this which doesn't require the use of transactions is to
get the sequence value first, then use it explicitly. Here's some
pseudocode to do that:

the_id = select NEXTVAL('test_id_seq');
insert into test(id, name) values (the_id, 'something');

And at this point 'the_id' is your sequence ID.

--
Colin Fox
President
CF Consulting Inc.
cfox - at - cfconsulting - dot - ca

Nov 11 '05 #4

P: n/a
> Here's an example series of SQL that should do what you want:

begin;
insert into test(name) values ('something');
select CURVAL('test_id_seq');
commit;

At the end, you must either commit(save changes) or rollback(undo
changes).

Another way to do this which doesn't require the use of transactions is
to get the sequence value first, then use it explicitly. Here's some
pseudocode to do that:

the_id = select NEXTVAL('test_id_seq'); insert into test(id, name)
values (the_id, 'something');

And at this point 'the_id' is your sequence ID.


This is exactly what i needed, and it works.
BTW, "currval" is spelled with two 'r'.

Thanks a lot for your help !
Nov 11 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.