Search code examples
sqlpostgresqlsequencealterpostgresql-9.5

Alter sequence to be linked to another table

So I'm uploading some 150,000 rows of data into a database over HTTP via Python backend, and the upload takes a while, thus I'm inserting it into a new table which I then swap with (by renaming) the old table:

create table tmp (like main);
alter sequence main_id_seq restart;
alter table tmp alter column id set default nextval('main_id_seq');
drop table main cascade;  -- THIS REMOVES THE SEQUENCE ^^^^^^^
alter table tmp rename to main;

How can I alter the sequence to not be linked to the main table, so that when I drop the main table, the sequence would stay linked to the current tmp table (new main)?

Solution

  • You can do that by making the column "owning" the sequence

    alter sequence main_id_seq
  owned by main.id;