Change sequence name in postgreSQL

Question

I would like to change the name of my sequence I use. I tried those two options and failed:

ALTER TABLE PLAYER RENAME id_seq_player TO player_id_seq;

and

ALTER SEQUENCE id_seq_player RENAME TO player_id_seq;

I looked at the official documentation and from there I created those two SQL, but none of them worked. The first solution resulted in a SQL error and the second had a time out.

Here is the SQL error I get:

ERROR:  column "id_seq_player" does not exist

********** Error **********

* UPDATE *

Seems like the second SQL statement did the job. Since I have to forward the port, maybe it was an issue with the connection or OpenShift. But now I retried several times to verify and it works.

ALTER SEQUENCE id_seq_player RENAME TO player_id_seq; /* Works */

I do. I only use one user and that user created all tables with the corresponding sequences. But I will double check in my logs if there is something weird going on. — Jernej K
– Jernej K, Commented Apr 1, 2016 at 17:38

Anthony Drogon · Accepted Answer · 2016-04-01 17:54:17Z

74

ALTER SEQUENCE id_seq_player RENAME TO player_id_seq;

is correct. You might want to add the schema name as well to ensure you are altering the correct one, but this should most likely work.

If it timeouts, you might have another process that is locking your sequence. Is there a way for you to turn off all the other database users, or is it too critical to do so?

answered Apr 1, 2016 at 17:54

Anthony Drogon

1,9041 gold badge19 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shubham Batra · Accepted Answer · 2016-04-02 05:07:46Z

6

Try this:

ALTER TABLE id_seq_player RENAME TO player_id_seq;

answered Apr 2, 2016 at 5:07

Shubham Batra

2,3755 gold badges32 silver badges48 bronze badges

Comments

Juan Carlos Oropeza · Accepted Answer · 2016-04-01 17:01:24Z

0

Dont know the command line but you can change it on the pgAdmin

I just add 1 to my sequence and change it back.

answered Apr 1, 2016 at 17:01

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

4 Comments

Jernej K Over a year ago

I want the SQL statement, because I have several sequences, where I would like to rename them. And I do not want to do that manually for each of them. I am only using pgAdmin, because that is the only way I can connect to the database since I use Openshift. But thanks, I will keep that in mind.

Jernej K Over a year ago

And the SQL you forwarded basically not renames but creates a new one. I do not like the SQL it uses. I know there is a rename SQL statement I could use.

Juan Carlos Oropeza Over a year ago

No. pgAdmin show you the create, But I show you the left side where I right click properties and change the name.

Jernej K Over a year ago

Like I wrote in the second comment, it does remove the current and creates a new one. I do not like that and it is also odd the start value is 1. I will continue to play with this solution.

Collectives™ on Stack Overflow

Change sequence name in postgreSQL

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related