currval has not yet been defined this session, how to get multi-session sequences?

Question

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

Answer 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.