Thread: Last insert id

Last insert id

From

mixo

Date:

09 June 2004, 06:24:47

I have three tables which are related a serial field, table1_id, in on 
of the tables. Updating the tables is done through a transaction. My 
problem is, once I have insert  a row in the first tables with 
table1_id, I need for the other two tables. How can I get this? Is it 
safe to use "select max(table1_id) from table1" after the insert?

Re: Last insert id

From

Date:

09 June 2004, 06:54:56

I'm new on postgreSQL, so this might not be the simplest sollution:
Use a sequence instead of serial.
After you have generated the new id with your_seq.nextval
you can get thesame number again with  your_seq.currval.

details at  http://www.postgresql.org/docs/7.3/static/functions-sequence.html

mixo schrieb:

>I have three tables which are related a serial field, table1_id, in on 
>of the tables. Updating the tables is done through a transaction. My 
>problem is, once I have insert  a row in the first tables with 
>table1_id, I need for the other two tables. How can I get this? Is it 
>safe to use "select max(table1_id) from table1" after the insert?
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>

Re: Last insert id

From

Richard Huxton

Date:

09 June 2004, 07:23:07

mixo wrote:
> I have three tables which are related a serial field, table1_id, in on 
> of the tables. Updating the tables is done through a transaction. My 
> problem is, once I have insert  a row in the first tables with 
> table1_id, I need for the other two tables. How can I get this? Is it 
> safe to use "select max(table1_id) from table1" after the insert?

A SERIAL column is just an ordinary integer with a default of 
nextval(<sequence-name>).

All you need to do is:
INSERT INTO t1 (...) VALUES (...)
INSERT INTO t2 (...) VALUES (currval(<sequence-name-from-table1>)...)

Sequences are guaranteed to give values relevant to your current connection.

--   Richard Huxton  Archonet Ltd

Re: Last insert id

From

"Andrei Bintintan"

Date:

15 June 2004, 07:05:44

"Is it safe to use "select max(table1_id) from table1" after the insert?"

Yes it is safe, but ONLY if you use it inside a transaction.(BEGIN/COMMIT).

BR.


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of mixo
Sent: Wednesday, June 09, 2004 9:24 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Last insert id

I have three tables which are related a serial field, table1_id, in on of
the tables. Updating the tables is done through a transaction. My problem
is, once I have insert  a row in the first tables with table1_id, I need for
the other two tables. How can I get this? Is it safe to use "select
max(table1_id) from table1" after the insert?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html

Re: Last insert id

From

Rod Taylor

Date:

15 June 2004, 10:30:19

On Tue, 2004-06-15 at 03:05, Andrei Bintintan wrote:
> "Is it safe to use "select max(table1_id) from table1" after the insert?"
> 
> Yes it is safe, but ONLY if you use it inside a transaction.(BEGIN/COMMIT).

No, this is not safe outside of the serializable isolation.

rbt=# begin;
BEGIN
rbt=# select max(id) from l;max
-----  1
(1 row)

rbt=# insert into l values (2);
INSERT 62597 1
rbt=# select max(id) from l;max
-----  3
(1 row)

rbt=# commit;
COMMIT

I inserted 3 from another connection after 2 was inserted.

Re: Last insert id

From

Michalis Kabrianis

Date:

21 June 2004, 03:59:40


Andrei Bintintan wrote:
> "Is it safe to use "select max(table1_id) from table1" after the insert?"
> 
> Yes it is safe, but ONLY if you use it inside a transaction.(BEGIN/COMMIT).
> 
> BR.
> 

Hi,
I think it would be safe to use :
select currval('tablename_idname_seq');
inside a session to receive the current value of the sequence that feeds 
the serial column (i.e. the last inserted value on that session). Every 
other session is irrelevant (as the currval returns the session-specific 
value).
If I understand correctly, it doesn't even need the transaction to be 
open (in which case all consecutive inserts on the same table would block).

M.K.
> 
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
> On Behalf Of mixo
> Sent: Wednesday, June 09, 2004 9:24 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Last insert id
> 
> I have three tables which are related a serial field, table1_id, in on of
> the tables. Updating the tables is done through a transaction. My problem
> is, once I have insert  a row in the first tables with table1_id, I need for
> the other two tables. How can I get this? Is it safe to use "select
> max(table1_id) from table1" after the insert?
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html