1

Say I have a table with an id BIGSERIAL column. I just started a new session. I want to know what the id of the next row I insert will be. How can I tell this?

I can't simply SELECT MAX(id) FROM my_table (and return 1 if none found) because if I previously deleted things, the next id will not be 1 but will be higher.

I've already looked at other answers that use currval() and nextval(). They don't work unless I inserted within the current session... And I don't want to insert a row and see what ID I got, haha.

I'm about to lock the table and insert a lot of rows from a Python script on a client that is also keeping track of row IDs in a dictionary. The row IDs must match up, so I need to know what the first one will be. I'm surprised nobody else is dealing with this scenario.

Edit: I was wrong about the functions not working unless I inserted within the current session, but see the comments below.

closed as off-topic by ypercubeᵀᴹ, Philᵀᴹ, RolandoMySQLDBA, Andriy M, mustaccio Feb 10 '16 at 20:18

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – ypercubeᵀᴹ, Philᵀᴹ, RolandoMySQLDBA, Andriy M, mustaccio
If this question can be reworded to fit the rules in the help center, please edit the question.

4

I want to know what the id of the next row I insert will be. How can I tell this, without inserting a row?

This is exactly what nextval() does. But I can't think of any useful thing to do with this value, if you are not inserting into the table. It will be the id value of a row that may never be inserted:

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.


As stated above, the value returned by nextval() is hardly ever useful, unless you insert into the table. But if you are inserting some rows and you simply want the id values of the serial, you can simply use the RETURNING clause in the INSERT:

insert into t (col1, col2, ...) 
values ( (value_a_col1, value_a_col2, ...),
         (value_b_col1, value_b_col2, ...),
         ...
       )
returning t_id ;

See an example at SQLfiddle.

Some more complicated cases - like when the ids are needed for inserting into more tables, related through foreign keys, or when an UPSERT is need - can be written in one statement, using RETURNING and data-modifying CTEs.
Note that UPSERT has been implemented in 9.5, with the INSERT ... ON CONFLICT DO NOTHING/UPDATE construct.
Examples below:

If you want the id values to be used in different columns, see this:

  • Yep, I was wrong about nextval(); not sure why I thought it wasn't working. This is close to what I need. It's too bad that it doesn't roll back. Luckily, in my specific case, this doesn't matter, but thanks for putting that warning in. – sudo Feb 10 '16 at 21:39
  • Also, note that calling nextval() actually increments the serial counter. That is, if you call it 6 times, currval will now be 6. The next row inserted will have ID 7. So really, you need to look at nextval() + 1. – sudo Feb 10 '16 at 21:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.