How can I change the sequence_name parameter after renaming a sequence (PostgreSQL)

Question

I have renamed a sequence with the following command:

ALTER SEQUENCE TableIdSeq RENAME TO NewTableIdSeq;

however, when I issue the \d NewTableIdSeq command, I get the following output:

       Sequence "dev.newtableidseq"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | tableidseq            <--------------- HASN'T CHANGED!!
 last_value    | bigint  | 3
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 30
 is_cycled     | boolean | f
 is_called     | boolean | t

As you can see, the sequence_name property of the sequence is still set to the old name. How can I change the name here as well?

Answer 1

Don’t worry about it. The sequence name stored inside the sequence is ignored. This field is gone in PostgreSQL 10.

Answer 2

You should rename the sequence and then alter the default value for this column.

Let me generate an example:

create sequence tbl_id_seq;

create table tbl 
(
    id int default nextval('tbl_id_seq'), 
    f1 int, 
    f2 int
);

insert into tbl (f1, f2) values (1,2),(3,4),(5,6);    
select * from tbl;

id | f1 | f2
-: | -: | -:
 1 |  1 |  2
 2 |  3 |  4
 3 |  5 |  6

-- rename sequence
alter sequence tbl_id_seq rename to tbl_id_new_seq;

-- alter default value 
alter table tbl alter column id set default nextval('tbl_id_new_seq');

-- restart sequence
alter sequence tbl_id_new_seq restart with 1;

-- check it by inserting new values
insert into tbl (f1, f2) values (1,2),(3,4),(5,6);
select * from tbl;

id | f1 | f2
-: | -: | -:
 1 |  1 |  2
 2 |  3 |  4
 3 |  5 |  6
 1 |  1 |  2
 2 |  3 |  4
 3 |  5 |  6

Now using \d tbl

+-------------------------------------------------------------------------------+
|                               Table "public.tbl"                              |
+-------------------------------------------------------------------------------+
| Column |   Type  | Collation | Nullable |               Default               |
+--------+---------+-----------+----------+-------------------------------------+
| id     | integer |           |          | nextval('tbl_id_new_seq'::regclass' |
+--------+---------+-----------+----------+-------------------------------------+
| f1     | integer |           |          |                                     |
+--------+---------+-----------+----------+-------------------------------------+
| f2     | integer |           |          |                                     |
+--------+---------+-----------+----------+-------------------------------------+

And for \d tbl_id_new_seq:

postgres=# \d tbl_id_new_seq;
Sequence "public.tbl_id_new_seq"
-[ RECORD 1 ]------------------
Type      | bigint
Start     | 1
Minimum   | 1
Maximum   | 9223372036854775807
Increment | 1
Cycles?   | no
Cache     | 1