PostgreSQL 9.2; Altering a table to use another sequence

postgresqlpostgresql-9.2

I've created a table like so:

create table doki_data_item2 (like doki_data_item including defaults);

but this new table uses the same sequence as the old table.

So two questions:

  1. How can I copy and table and the necessary sequences easily?
  2. How can I replace a sequence on one table with another sequence?

Best Answer

Why don't you create a new sequence:

create sequence new_sequence;

Then create the new table

create table doki_data_item2 (like doki_data_item including defaults);

Finally modify the column that is using the old sequence to use the new one:

alter table doki_data_item2 alter column seq_column set default nextval('new_sequence');

You can obviously set various parameters for the newly created sequence to suit your needs.

Related Question