6

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?

3

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

| improve this answer | |
0

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
| improve this answer | |
  • 2
    But I'm not trying to alter the default value of any column... I just want the new name of the sequence to be visible in the sequence_name field of the sequence properties (which can be seen when you issue the \d sequence command) – Daniel Gray Mar 19 '18 at 18:40
  • Once again, I'm not doing \d table... I'm doing \d sequence. And the name is not reflected in the result of the \d sequence command. – Daniel Gray Mar 21 '18 at 6:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.