currval has not yet been defined this session, how to get multi-session sequences?
Solution 1:
The currval
will return the last value generated for the sequence within the current session. So if another session generates a new value for the sequence you still can retrieve the last value generated by YOUR session, avoiding errors.
But, to get the last generated value on any sessions, you can use the above:
SELECT last_value FROM your_sequence_name;
Be careful, if the value was used by other session with an uncommited (or aborted) transaction and you use this value as a reference, you may get an error. Even after getting this value it may already be out of date. Generally people just need the currval
or even the return of setval
.
Solution 2:
This may be simpler than you think ...
My objective is to get a primary key field automatically inserted when inserting new row in the table.
Just set the default value of the column:
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval('my_seq'::regclass);
Or simpler yet, create the table with a serial
type for primary key to begin with:
CREATE TABLE tbl(
tbl_id serial PRIMARY KEY
,col1 txt
-- more columns
);
It creates a dedicated sequence and sets the default for tbl_id
automatically.
In Postgres 10 or later, consider an IDENTITY
column instead. See:
- Auto increment table column
This way tbl_id
is assigned the next value from the attached sequence automatically if you don't mention it in the INSERT
. Works with any session, concurrent or not.
INSERT INTO tbl(col1) VALUES ('foo');
If you want the new tbl_id
back to do something with it:
INSERT INTO tbl(col1) VALUES ('foo') RETURNING tbl_id;