PostgreSQL question!

[Anon]

Hey all..

I've got an INSERT INTO statement and need to get the SERIAL value of a field of the row i just inserted for a subsequent INSERT statement... how would I do this?

Thanks!

-Surojit

[Anon]

hi
use ;
select max(col_name) from table;

[[deleted]]

No, never EVER do this.

This will give you tha absolute highest number, which is NOT the last number you inserted. (multi-user remember?)

Instead, use pg_getlastoid(), which will return the last ID assigned by queries run over the same connection as you are doing this request on.
http://www.php.net/manual/en/function.pg-getlastoid.php

[bfranco]

After you execute your INSERT SQL, you can call pg_getlastoid(). It will give you the unique value of the oid field of the row just inserted.
You can then execute a SELECT to get any field from that row.

Example:

//IN POSTGRESQL
CREATE TABLE test(
id SERIAL,
description TEXT
);

//IN PHP
$con=pg_connect(...);

//Insert the row
$res=pg_exec("INSERT INTO test (description) VALUES ('First Record')");

//Get the oid
$OID=pg_getlastoid($res);

//Get the record back
$res=pg_exec("SELECT * FROM test WHERE oid=$OID");

You can see the oids in a query doing
SELECT oid, * FROM {TABLENAME}

Hope it helps.

[Anon]

Interesting...

but how efficient is this? The idea was to use the least number of database "trips" to get this value... I thought there was a way to have a trigger or function return the "autoincrement" field in the INSERT INTO statement...

what's the "@" sign do when placed in front of an attribute?

Thanks!

-Surojit

[[deleted]]

The only important line in that entire post was

$OID=pg_getlastoid($res);

which gets the number you are looking for,
the rest is just an example of how to use it.

The @ makes it so that no errors are displayed. Evil. Don't use it unless you know what you are doing and have proper error checking built into your script.

[Anon]

how about when you use it in the context of

SELECT @contact_company_id FROM contact_company;

contact_company_id is a SERIAL field.

-S

[[deleted]]

I have never seen the @ being used in a query.

[bfranco]

ok... you don't like it. I'll give ya another solution.

You can get your id BEFORE you insert the row, something like
SELECT nextval('test_id_seq');
And then, you insert your row. Still you need 2 queries, but you can put everything in 1 stored procedure (I'll use the test table from the previous message):

CREATE FUNCTION TestInsert(Text)
RETURNS INTEGER
AS '
DECLARE newid INTEGer;
BEGIN
SELECT INTO newid nextval(\'test_id_seq\');
INSERT INTO test (id, description) VALUES(newid, $1);
RETURN newid;
END;' LANGUAGE 'plpgsql';

You can use it this way:
SELECT TestInsert("This is a new row");
SELECT TestInsert("This is a another row");

and it will return the field "id" for each inserted row.

[[deleted]]

ok ok, enough of this, it is getting silly...

(and VERY inefficient too)

[Anon]

so is the getlastoid method the most efficient of all the methods described in this thread?

-S

[[deleted]]

yup.

The MAX() method is just plain wrong,
and the stored procedures method is... well... less than efficient. Clever, but not efficient.

[Anon]

well then!

thanks a LOT for everybody who participated in this discussion... pgsql rocks!

-S

[bfranco]

you think so??? (talking bout the stored procedure method). It requires 2 queries (the select and the insert), just like the other method, but everything executes inside the backend...

I'm not saying that you're wrong, I just care about your opinion. I've had the same problem with Interbase, but it doesn't have oids, so working with stored procs was the best solution that came to my mind.

Parhaps the best thing would be to use the table's oid as the primary key, what do ya think?

[[deleted]]

"you think so???"

definately.

"I've had the same problem with Interbase, but it doesn't have oids,"

well that's interbase for you :-)
Different databases have different solutions, so what might work for interbase may be completely 'over the top' for other databases.

It's interesting to see different solutions though.

"arhaps the best thing would be to use the table's oid as the primary key, what do ya think?"

You should always use an automatically generated value for the primary key, because that is the only way of being sure that you will get a unique value very time (without resorting to massive locking strategies)
After you insert a record, the newest value for the serial column is already available in memory, so requesting it from the database does not require a real query, just a read on a memory location.

[bfranco]

I think that asking for the key first and using it for the insert is the fastest way to do the job.

if you insert the record, and then you execute the select using the OID as the condition, the server has to use the index to do the search, and the index pages may not be in memory.

anyway, we are talking bout very extreme cases. For regular database work both methods will work just fine.

[[deleted]]

Note: this is all written with the best intentions, no offence, a good discussion is educational, everybody learns, it's christmas etc etc...

Ok, let's get back to reality here.

The original question was "how can I get the new value for the SERIAL column after I insert a new record?"

The simplest and most efficient way to do this is by use pg_getlastoid(). Simply because that is returned immediately by the database engine and requires no extra query.

"I think that asking for the key first and using it for the insert is the fastest way to do the job."

When did doing extra work start making things faster?

"if you insert the record, and then you execute the select using the OID as the condition, the server has to use the index to do the search, and the index pages may not be in memory."

That's the point, there is no select, only an insert. (btw index pages not in memory? which databases server would ever take the index out of memory?)

"anyway, we are talking bout very extreme cases. For regular database work both methods will work just fine."

With all due respect: that is nonsense.
The fact that you are doing it on a small scale is no excuse for doing it 'wrong'.
That's how Microshit got into trouble.

[bfranco]

"The simplest and most efficient way to do this is by use pg_getlastoid()."

Remember that the OID has nothing to do with the SERIAL field, its a completily independent field.
It DOES require an extra query to get the record back, remember that you need to do a SELECT where the condition is the OID:

"SELECT id FROM test WHERE oid=".pg_getlasoid() <--- you see??? thats the query I'm talking about.

And this query uses an index. And pgsql uses a fixed buffer pool to store the pages, some kind of cache, and when it runs out of buffers has to swap in/out pages from memory, just like any other rdbms.

And I don't think that either solution is wrong. I think it's a matter of taste.
And I don't care bout what M$ does...

[[deleted]]

"Remember that the OID has nothing to do with the SERIAL field, its a completily independent field."

That is correct. I was confusing pgsql with mysql.

If you use a serial then you should indeed run a second query to get the new value. But if you use the OID then you don't.