How to get the next sequence value in PostgreSQL?

Question

Current I have a problem in finding the next sequence value of the column.

The my_list table is created by

CREATE SEQUENCE my_list_id_seq;
CREATE TABLE IF NOT EXISTS
my_list (id int PRIMARY KEY UNIQUE NOT NULL DEFAULT nextval('my_list_id_seq'),
mycardno);
ALTER SEQUENCE my_list_id_seq OWNED BY my_list.id;

I tried to use currval query to find the next sequence value (should be 1) but I get

SELECT currval('my_list_id_seq');
ERROR:  currval of sequence "my_list_id_seq" is not yet defined in this session

If I use nextval query, I will get 2, which is not I want and the sequence will increase 1 by this Query.

SELECT nextval('my_list_id_seq');

 nextval
---------
       2
(1 row)

After the nextval query, I can now use currval query.

SELECT currval('my_list_id_seq');

 currval
---------
       2
(1 row)

As I cannot find other solution at this stage, currently my step to get the is to

1. Do nextval and get the "next sequence" value
2. Use ALTER SEQUENCE my_list_id_seq RESTART WITH "next sequence - 1"; to set back the sequence value. The "next sequence - 1" is done by C# (I use WPF).

I would like to know if there are direct approach for this problem. Thank you.

Answer 1

As per the documentation, currval only works when nextval has been called in a session:

Returns the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.)

If you want to get the current value, you may want to try SELECT * FROM my_list_id_seq and use the corresponding value from the last_value column:

postgres=# CREATE SEQUENCE my_list_id_seq;
CREATE SEQUENCE
postgres=# CREATE TABLE IF NOT EXISTS
my_list (id int PRIMARY KEY UNIQUE NOT NULL DEFAULT nextval('my_list_id_seq'),
mycardno int);
CREATE TABLE
postgres=# ALTER SEQUENCE my_list_id_seq OWNED BY my_list.id;
ALTER SEQUENCE
postgres=# select * from my_list_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
(1 row)