54

My objective is to get a primary key field automatically inserted when inserting new row in the table.

How to get a sequence going from session to session in PostgreSQL?

 doubleemploi@hanbei:/home/yves$ psql -d test
 Mot de passe : 
 psql (8.4.13)
 Saisissez « help » pour l''aide.

 test=> create sequence test001 start 10;
 CREATE SEQUENCE
 test=> select currval('test001');
 ERREUR:  la valeur courante (currval) de la séquence « test00 » n''est pas encore définie dans cette session
 --- current value not yet defined this session (???)
 test=> select setval('test001', 10);
 setval 
 --------
      10
 (1 ligne)

 test=> select currval('test00');
  currval 
 ---------
       10
 (1 ligne)

 test=> \q
 test@hanbei:/home/yves$ psql -d test
 Mot de passe : 
 psql (8.4.13)
 Saisissez « help » pour l''aide.

 test=> select currval('test001');
 ERREUR:  la valeur courante (currval) de la séquence « test00 » n''est pas encore définie dans cette session
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
MUY Belgium
  • 2,330
  • 4
  • 30
  • 46
  • 1
    The whole purpose of a sequence is to have it session local. You can not "hand" the currval over to another session. Why do you think you need that? Why not do everything in a single transaction? –  Sep 18 '12 at 16:47
  • @a_horse_with_no_name because I have two session opened at the same time : an admin one and a production one. So I have to have two separate sessions. – MUY Belgium Sep 18 '12 at 19:44
  • why not create a single sequence and then just share it between the two sessions? They're get unique ids, job done. – Scott Marlowe Sep 18 '12 at 22:31

5 Answers5

148

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.

rogerdpack
  • 62,887
  • 36
  • 269
  • 388
MatheusOl
  • 10,870
  • 3
  • 30
  • 28
  • How to get the last value generated by other session. If i am using a singer DB User will it still effect accuracy? – Volatil3 Feb 23 '14 at 19:17
  • 8
    Actionally It is interesting, why the possibility to `SELECT * FROM sequence` is not even mentioned in official documentation ? – Eugene Dec 06 '17 at 00:06
  • 1
    OK I asked on the documentation and they said "oh there's a link to CREATE SEQUENCE that mentions it" so here it is, slightly hidden: https://www.postgresql.org/docs/8.1/sql-createsequence.html – rogerdpack Oct 19 '20 at 22:29
  • That's more safe – mark_infinite May 06 '21 at 20:38
  • This doesn't actually work: It returns `1` when the sequence is uninitialised, and again `1` when the first row is inserted into the corresponding table. It feels like a bug - it should return `0` before the first row is inserted. – Bohemian Mar 02 '22 at 05:01
23

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:

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
15

I will give a practical answer for this matter. My database server is used by my programs and my psql terminal; so there are multiple sessions. currently I am in my psql terminal:

fooserver=> select currval('fusion_id_seq');
ERROR:  currval of sequence "fusion_id_seq" is not yet defined in this session
fooserver=> select nextval('fusion_id_seq');
 nextval 
---------
  320032
(1 row)

fooserver=> select currval('fusion_id_seq');
 currval 
---------
  320032
(1 row)

It looks that you can only see the values in your own session. This will also affect the currval of another session. This is probably related to multi-threading of the server to isolate different session. The counter (serial in psql) is a shared object. In my opinion, this session should be able to get the current value of the counter as long as the counter is properly locked to ensure only a single thread (session) can increment it (atomic operation). But I could be wrong here (not an expert on database server writer).

Kemin Zhou
  • 6,264
  • 2
  • 48
  • 56
4

Actually nextval will advance sequence and return the new value, so that would be the answer for your question.

currval will return the value most recently obtained with nextval for specified sequence (This though might fail if there wasn't nextval used in current session).

Jalal
  • 407
  • 3
  • 5
1

This issue seems to be intermittent , For consistency use CTE to get inserted sequence for current session 

WITH inserted AS ( 
         INSERT INTO notifn_main (notifn_dt,stat_id)
     SELECT now(),22 
     FROM notifn 
     RETURNING id )
     SELECT id 
     from inserted INTO tmp_id;
Kerem
  • 11,377
  • 5
  • 59
  • 58
Sandeep naik
  • 11
  • 2