3

I'm running an INSERT query from one php file, but then I need to get the last inserted id from an external php file so as to run another query based on that id. How can I pull this off using CURRVAL or RETURNING? From the external php file, if I do something like

$result = pg_query($db,"SELECT CURRVAL('app.example_id_seq'), ... "); I get no results.

Clint_A
  • 518
  • 2
  • 11
  • 35

2 Answers2

6

You can query the sequence as if it were a table:

SELECT last_value
FROM app.example_id_seq
WHERE is_called;

This will return no result if the sequence has never been used (then is_called is FALSE).

But this is a bad value to determine the ID of a newly inserted table row. It will only work if nobody else has used the sequence after the row was inserted. Also, you cannot figure out if the insertion failed or not.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

Not sure what that tree dots in your example are for. However:

select currval('app.example_id_seq'::regclass);

Should work. Sometimes it can fail with:

ERROR:  55000: currval of sequence "example_id_seq" is not yet defined in this session

So nothing called nextval before currval. But if you called insert into which worked, that currval must work as well.

To start using RETURNING, please visit docs - there are examples provided. Basically you are listing column names that should be in result of insert, or update.

Michał Zaborowski
  • 3,911
  • 2
  • 19
  • 39
  • Does that mean I should use `nextval` in the `INSERT` before I can use `currval` in the `SELECT`? If yes, what if the `id` column is auto incremented? – Clint_A Jan 15 '18 at 14:06
  • if your table definition has `nextval` in default value for id column - it will increment it by it's own. So just provide other columns in your insert, and all will work just fine – Michał Zaborowski Jan 15 '18 at 14:09
  • So what happens if the table doesn't have `nextval` by default? What about the `RETURNING id`? how can I call it from another php file? – Clint_A Jan 15 '18 at 14:25
  • `currval` works only in the same session than a preceding `nextval`. But it looks like "from another php file" means for you "from another database session" or "from another HTTP request" which amounts to the same. Laurenz Albe's answer fits that case. – Daniel Vérité Jan 15 '18 at 14:47
  • @DanielVérité if there are two inserts, at same time? – Michał Zaborowski Jan 15 '18 at 14:58
  • @MichałZaborowski: it's up to the asker to know whether it's relevant to get the last ID inserted, without knowing to what entry it refers to and if there have been other inserts in the meantime. As a design it looks fragile and unsuitable to concurrent uses, as mentioned in Laurenz's answer. – Daniel Vérité Jan 15 '18 at 15:08