On 12/12/2013 08:24 PM, Tim Uckun wrote:
I have a table foo. It has a serial column called "id". I execute the
following statement
ALTER TABLE table_name RENAME TO archived_table_name;
CREATE TABLE table_name (LIKE archived_table_name INCLUDING
DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
..... Archieve the table here...
DROP TABLE arhived_table_name
This doesn't work because the archived table name has a dependency on
the sequence created by the serial field. So I try to remove that
dependency by doing this.
alter table "archived_table_name" alter column id drop default;
ALTER TABLE"archived_table_name" DROP CONSTRAINT systemevents_pkey;
So by now there should not be a dependency on the sequence but I still
can't drop the table and and pgadmin tells me it's still depending on
the sequence.
When I look at the table definition it doesn't seem to have any
reference to the sequence at all.
How can I drop this table and leave the sequence alone? Obviously the
newly created table needs it.
In addition to what David said here is another option, create the
original table with a non-dependent sequence:
test=> CREATE SEQUENCE shared_seq;
CREATE SEQUENCE
test=> create table seq_test(id integer default nextval('shared_seq'),
fld varchar);
CREATE TABLE
test=> ALTER TABLE seq_test RENAME to archived_seq_test;
ALTER TABLE
test=> CREATE TABLE seq_test (LIKE archived_seq_test INCLUDING DEFAULTS
INCLUDING CONSTRAINTS INCLUDING INDEXES);
CREATE TABLE
test=> DROP TABLE archived_seq_test;
DROP TABLE
When you use the serial type it creates a dependency on the serial and
as David pointed out you can do the same thing with ALTER SEQUENCE.
However as shown above there is no requirement that a sequence be
dependent. It is at its core a 'table' that is a number generator.
Thanks.
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
