How to get id after insert query in PostgreSQL?

Member

by aubrey , in category: PHP Databases , 2 years ago

How to get id after insert query in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

Member

by rollin , 2 years 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.

by cali_green , a year ago

@aubrey 

To get the ID after an insert query in PostgreSQL, you can use the RETURNING clause.


Here's an example:

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


Replace your_table_name, column1, column2, column3 with the actual table and column names, and value1, value2, value3 with the values you want to insert.


The RETURNING clause after the VALUES statement will return the inserted ID in the result set. You can then fetch this ID using your programming language or use it directly in subsequent queries.


Note that the RETURNING clause is only available in PostgreSQL version 8.2 and above.