I want to get id of current inserted row after executing insertion query.
p.s.: I'm using postgresql database.
A:
In PG7 it was pg_last_oid, the docs there explain pretty clearly how to get the last id from newer version of PG.
RaYell
2009-08-26 08:20:43
when this query is executed "INSERT INTO contacts (title,name,job_title,phone,email) VALUES ('tes','test s/w','0102365478','[email protected]','123') RETURNING id;"it result the error: Query failed: ERROR: syntax error at or near "RETURNING"
2009-08-26 09:53:28
I think that should be comment to garrow's or Milen A. Radev's answers
RaYell
2009-08-26 09:57:44
sorry it is a conflict.
2009-08-26 10:02:17
+3
A:
If we assume that by "id" you mean a column which is primary key and also is declared SERIAL then you need to add a RETURNING to the INSERT statement:
INSERT INTO <table> (...) VALUES (...) RETURNING id;
And in PHP you can treat this statement as a normal query which returns one row.
Milen A. Radev
2009-08-26 08:25:44
when this query is executed "INSERT INTO contacts (title,name,job_title,phone,email) VALUES ('tes','test s/w','0102365478','[email protected]','123') RETURNING id;" it result the error: Query failed: ERROR: syntax error at or near "RETURNING"
2009-08-26 09:59:26
Which probably means you're using older version (<8.2) of Postgres which does not support RETURNING. If possible upgrade.
Milen A. Radev
2009-08-26 10:27:48
is there any solutions??i tried currval() method but this error is appeared : "Query failed: ERROR: currval of sequence "contacts_id_seq" is not yet defined in this session"
2009-08-26 11:30:32
Please provide details - server version, description of the table(s) involved and the exact command sequence you tried.
Milen A. Radev
2009-08-26 14:17:05
+1
A:
From the Postgres documentation
Insert a single row into table distributors, returning the sequence number generated by the DEFAULT clause:
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;
http://www.postgresql.org/docs/8.3/interactive/sql-insert.html
garrow
2009-08-26 08:28:13