How to get id after insert query in PostgreSQL?

Member

by aubrey , in category: PHP Databases , 8 months ago

How to get id after insert query in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by rollin , 3 months ago

@aubrey 

In PostgreSQL, you can use the RETURNING clause in your INSERT statement to return the ID of the inserted row.


Here is an example of how you can use the RETURNING clause to return the ID of an inserted row:

1
2
3
INSERT INTO tablename (column1, column2, column3)
VALUES (value1, value2, value3)
RETURNING id;


The RETURNING clause can be used with any INSERT, UPDATE, or DELETE statement and allows you to return values from the affected rows.


You can also use the LASTVAL() function to return the last inserted ID. To do this, you can run a separate SELECT statement after the INSERT statement like this:

1
2
3
4
INSERT INTO tablename (column1, column2, column3)
VALUES (value1, value2, value3);

SELECT LASTVAL();


Keep in mind that LASTVAL() will return the ID of the last inserted row, regardless of which table the row was inserted into or which session created the row.


I hope this helps! Let me know if you have any questions.