Rename nextval('...') in Postgres

Question

I had a table called pivot_device_user and had a sequence on the id as not null default nextval('pivot_device_user_id_seq'::regclass).

Then I decided to rename my table to pivot_box_user, but the nextval(...) is still nextval('pivot_device_user_id_seq'::regclass).

I'd like to change it to nextval('pivot_box_user_id_seq'::regclass). How do I do this?

Answer 1

First you must understand what a serial really is:

• Safely and cleanly rename tables that use serial primary key columns in Postgres?
• Auto increment SQL function

The column default is not actually stored as text literal. What you see is just the human-readable text representation: nextval('pivot_device_user_id_seq'::regclass)

'pivot_device_user_id_seq'::regclass is resolved to an OID internally (regclass to be precise) - the OID of the underlying sequence - and that's what's actually stored (early binding). If you rename the sequence, its OID remains unchanged. So all you need to do is rename the sequence:

ALTER SEQUENCE pivot_device_user_id_seq RENAME TO pivot_box_user_id_seq;

Check success with:

SELECT pg_get_serial_sequence('pivot_box_user', 'id');

Related:

• Activerecord-import & serial column in PostgreSQL