Getting the next bigserial value in PostgreSQL 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.
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:


How to Choose Between VALUES and SELECT for INSERT?
Idiomatic way to implement UPSERT in PostgreSQL
Inserting into related tables
PostgreSQL multi INSERT…RETURNING with multiple columns
Bulk insert in multiple tables


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


Reference value of serial column in another column during same INSERT