472,362 Members | 1,702 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,362 software developers and data experts.

Retrieving tuple data on insert

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
4 8034
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
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
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
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Alastair G. Hogge | last post by:
Hello *, I'm using Python and the cgi module to retrive data from a HTML form. I'm then trying to get that information into a string. But efforts fail when I run type(foo) on my string. type()...
10
by: Jinming Xu | last post by:
Hi Folks, I have a number sequence, which is put into a tuple like this: y=2, 3.0, 4.5 I can manipulate the sequence as a tuple when it has more than 1 number. But when the sequence has...
50
by: Will McGugan | last post by:
Hi, Why is that a tuple doesnt have the methods 'count' and 'index'? It seems they could be present on a immutable object. I realise its easy enough to convert the tuple to a list and do this,...
2
by: Rob Cowie | last post by:
Hi, Given a string representing the path to a file, what is the best way to get at the filename? Does the OS module provide a function to parse the path? or is it acceptable to split the string...
3
by: localpricemaps | last post by:
i am having a problem writing a tuple to a text file. my code is below. what i end up getting is a text file that looks like this burger, 7up burger, 7up burger, 7up and this is instead...
3
by: Susanne Klemm | last post by:
Hello! I use a procedure to insert a new row into a table with an identity column. The procedure has an output parameter which gives me the inserted identity value. This worked well for a long...
15
by: gunnar.sigurjonsson | last post by:
Im having some problem retrieving identity value from my newly inserted row into a view. I have two tables T1 and T2 which I define as following CREATE TABLE T1 ( id BIGINT GENERATED ALWAYS...
7
by: erikcw | last post by:
Hi, I'm trying to build a SQL string sql = """INSERT INTO ag ('cid', 'ag', 'test') VALUES(%i, %s, %d)""", (cid, ag, self.data) It raises this error: AttributeError: 'tuple' object has no...
0
bmallett
by: bmallett | last post by:
First off, i would like to thank everyone for any and all help with this. That being said, I am having a problem retrieving/posting my dynamic form data. I have a form that has multiple options...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
0
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.