Alter sequence to be linked to another table

Question

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

user330315 · Accepted Answer · 2017-05-11 07:08:52Z

3

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

alter sequence main_id_seq
  owned by main.id;

answered May 11, 2017 at 7:08

user330315

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

3 Comments

Mahi Over a year ago

Figured there must be a simple way like this, but couldn't find the owned by from anywhere... Thanks!

user330315 Over a year ago

@MarkusMeskanen: it's right here: postgresql.org/docs/current/static/sql-altersequence.html

Mahi Over a year ago

Yeah I gotta read the docs more, I'm still rather new with PSQL. Will make some further research next time, thanks :)

Vao Tsun · Accepted Answer · 2017-05-11 07:09:11Z

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

Collectives™ on Stack Overflow

Alter sequence to be linked to another table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related