There was a problem copying some tables from one schema to another and now I have a few tables with serial fields pointing another schema's sequences. I want to change that dependency.
I need to copy those sequences to the table schema and change the field to point the copies in the current table schema.
Table 1 definition:
CREATE TABLE schema1.table1
(
gid integer NOT NULL DEFAULT nextval('schema2.table1_seq'::regclass),
...
)
I want it to point schema1.table1_seq . Is it posible?
Use ALTER TABLE:
ALTER TABLE schema1.table1 ALTER gid SET DEFAULT nextval('schema1.table1_seq'::regclass);
schema1.table1_seq doesn't exist yet, does this create a sequence for gid beginning at 1? If schema1.table1 has gids from 1 to 100, does this know to begin at gid 101? I think you'd need a start with clause like @VaoTsun suggests in their answer.it should be done itself while moving:
t=# create schema schema1;
CREATE SCHEMA
t=# create table schema1.a(i serial);
CREATE TABLE
t=# create schema schema2;
CREATE SCHEMA
t=# alter table schema1.a set schema schema2;
ALTER TABLE
t=# \d b.a
Table "schema2.a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------
i | integer | | not null | nextval('schema2.a_i_seq'::regclass)
but lets mock up your state:
t=# create sequence schema1.s;
CREATE SEQUENCE
t=# create table schema2.a(i int default nextval('schema1.s'::regclass));
CREATE TABLE
now you need:
t=# create sequence schema2.s start with 99;
CREATE SEQUENCE
t=# alter table schema2.a alter column i set default nextval('schema2.s'::regclass);
ALTER TABLE
with 99 to be actual number copied from schema1.s...
