Keep sequence created from BIGSERIAL when deleting table

Question

I have a postgres table creating with the following SQL:

CREATE TABLE mytable (
    mytable_id BIGSERIAL NOT NULL,
    mytable_char VARCHAR(8) NOT NULL
)

This creates the table as well as an implicit mytable_mytable_id_seq sequence.

Now, after creating 1.000.000 records, I want to split this table into partitioned tables (using inheritance). Because I link refer to the main table from other tables, I want to keep using the IDs from the original table in the new child tables and keep using the sequence.

However, if I do DROP TABLE mytable it also deletes the sequence. How can I keep the sequence when dropping the table?

Answer 1

You need to first remove the association between the column and the sequence:

alter sequence mytable_mytable_id_seq owned by none;

If you now drop the table, the sequence will not be dropped.

Details are in the manual: http://www.postgresql.org/docs/current/static/sql-altersequence.html

An alternative is to create a new sequence and set that to the value of the existing sequence:

create sequence part_seq;
select setval('part_seq', (select nextval('mytable_mytable_id_seq'), false);