CODEWITHSUNDEEP
sqlpostgresqlsequencealterpostgresql-9.5
Mahi
Mahi

Reputation: 21941

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)?

Upvotes: 2

Views: 1073

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51649

use alter sequence:

t=# create table s120(i bigserial);
CREATE TABLE
t=# \d+ s120;
                                            Table "public.s120"
 Column |  Type  |                    Modifiers                     | Storage | Stats target | Description
--------+--------+--------------------------------------------------+---------+--------------+-------------
 i      | bigint | not null default nextval('s120_i_seq'::regclass) | plain   |              |

t=# create table s121(i bigint);
CREATE TABLE
t=# alter sequence s120_i_seq owned by s121.i;
ALTER SEQUENCE
t=# drop table s120;
DROP TABLE
t=# alter table s121 alter COLUMN i set default nextval('s120_i_seq'::regclass);
ALTER TABLE
t=# \d+ s121
                                       Table "public.s121"
 Column |  Type  |                Modifiers                | Storage | Stats target | Description
--------+--------+-----------------------------------------+---------+--------------+-------------
 i      | bigint | default nextval('s120_i_seq'::regclass) | plain   |              |

Upvotes: 1

user330315
user330315

Reputation:

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

alter sequence main_id_seq
  owned by main.id;

Upvotes: 2

Related Questions