Как получить id после insert postgresql
$oid = pg_last_oid ( $res );
?>
- pg_query() — Выполняет запрос
- pg_result_status() — Возвращает состояние результата запроса
User Contributed Notes 6 notes
The way I nuderstand it, each value is emitted by a sequence only ONCE. If you retrieve a number (say 12) from a sequence using nextval(), the sequence will advance and subsequent calls to nextval() will return further numbers (after 12) in the sequence.
This means that if you use nextval() to retrieve a value to use as a primary key, you can be guaranteed that no other calls to nextval() on that sequence will return the same value. No race conditions, no transactions required.
Return the id after insert or select
I want to build a function which will insert an email if the email value doesn’t exist in the table and return the email_id of the row. How can I do this?
Also how can I return the id if the email was not inserted and it already exist in the DB? Do I need to perform another SELECT ?
I have tried adding the returning id but it doesn’t work. I got:
query has no destination for result data
2 Answers 2
@a_horse already explained how to avoid the error message you saw.
Here is a simple variant of the related version we have been referring to:
You only need the loop to deal with a possible race condition: If a concurrent transaction writes the same email value in between SELECT and INSERT , you would get a unique violation — which is handled properly here. This is assuming a UNIQUE constraint (or a UNIQUE index) on email , obviously.
The alternative with CTEs runs as one SQL statement. So here the overhead is slightly smaller (simpler queries), but the time frame for a race condition is slightly bigger. Especially if the row frequently already exists, this is a bit faster.
Read the detailed explanation over there and pick the approach that best fits your use case.
PostgreSQL function for last inserted ID
In PostgreSQL, how do I get the last id inserted into a table?
In MS SQL there is SCOPE_IDENTITY().
Please do not advise me to use something like this:
14 Answers 14
( tl;dr : goto option 3: INSERT with RETURNING )
Recall that in postgresql there is no «id» concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type).
If you are interested in getting the id of a newly inserted row, there are several ways:
The name of the sequence must be known, it’s really arbitrary; in this example we assume that the table persons has an id column created with the SERIAL pseudo-type. To avoid relying on this and to feel more clean, you can use instead pg_get_serial_sequence :
Caveat: currval() only works after an INSERT (which has executed nextval() ), in the same session.
This is similar to the previous, only that you don’t need to specify the sequence name: it looks for the most recent modified sequence (always inside your session, same caveat as above).
Both CURRVAL and LASTVAL are totally concurrent safe. The behaviour of sequence in PG is designed so that different session will not interfere, so there is no risk of race conditions (if another session inserts another row between my INSERT and my SELECT, I still get my correct value).
However they do have a subtle potential problem. If the database has some TRIGGER (or RULE) that, on insertion into persons table, makes some extra insertions in other tables. then LASTVAL will probably give us the wrong value. The problem can even happen with CURRVAL , if the extra insertions are done intto the same persons table (this is much less usual, but the risk still exists).
Option 3: INSERT with RETURNING
This is the most clean, efficient and safe way to get the id. It doesn’t have any of the risks of the previous.
Drawbacks? Almost none: you might need to modify the way you call your INSERT statement (in the worst case, perhaps your API or DB layer does not expect an INSERT to return a value); it’s not standard SQL (who cares); it’s available since Postgresql 8.2 (Dec 2006. )
Conclusion: If you can, go for option 3. Elsewhere, prefer 1.
Note: all these methods are useless if you intend to get the last inserted id globally (not necessarily by your session). For this, you must resort to SELECT max(id) FROM table (of course, this will not read uncommitted inserts from other transactions).
Conversely, you should never use SELECT max(id) FROM table instead one of the 3 options above, to get the id just generated by your INSERT statement, because (apart from performance) this is not concurrent safe: between your INSERT and your SELECT another session might have inserted another record.
Как получить id после insert postgresql
Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT , UPDATE , and DELETE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.
The allowed contents of a RETURNING clause are the same as a SELECT command’s output list (see Section 7.3). It can contain column names of the command’s target table, or value expressions using those columns. A common shorthand is RETURNING * , which selects all columns of the target table in order.
In an INSERT , the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values. For example, when using a serial column to provide unique identifiers, RETURNING can return the ID assigned to a new row:
The RETURNING clause is also very useful with INSERT . SELECT .
In an UPDATE , the data available to RETURNING is the new content of the modified row. For example:
In a DELETE , the data available to RETURNING is the content of the deleted row. For example:
If there are triggers (Chapter 39) on the target table, the data available to RETURNING is the row as modified by the triggers. Thus, inspecting columns computed by triggers is another common use-case for RETURNING .
| Prev | Up | Next |
| 6.3. Deleting Data | Home | Chapter 7. Queries |
Submit correction
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.