how to move tables from public to other schema in Postgres

Question

Postgres 9.1 database contains tables yksus1 .. ykssu9 in public schema. pgAdmin shows those definitions as in code below. How to move those tables to firma1 schema ?

Other tables in firma1 schema have foreign key references to those table primay keys. Foreign key references to those tables are only from tables in firma1 schema.

Some of those tables contain data. If tables is moved to firma1 schema, foreign key references shouuld also be updated to firma1.yksusn tables. Table structures cannot changed.

It looks like primary key sequences are already in firma1 schema so those should not moved. Version string PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

CREATE TABLE yksus1
(
  yksus character(10) NOT NULL DEFAULT ((nextval('firma1.yksus1_yksus_seq'::regclass))::text || '_'::text),
  veebis ebool,
  nimetus character(70),
  "timestamp" character(14) DEFAULT to_char(now(), 'YYYYMMDDHH24MISS'::text),
  username character(10) DEFAULT "current_user"(),
  klient character(40),
  superinden character(20),
  telefon character(10),
  aadress character(50),
  tlnr character(15),
  rus character(60),
  CONSTRAINT yksus1_pkey PRIMARY KEY (yksus)
);
ALTER TABLE yksus1
  OWNER TO mydb_owner;

CREATE TRIGGER yksus1_trig
  BEFORE INSERT OR UPDATE OR DELETE
  ON yksus1
  FOR EACH STATEMENT
  EXECUTE PROCEDURE setlastchange();

other tables are similar:

CREATE TABLE yksus2
(
  yksus character(10) NOT NULL DEFAULT ((nextval('firma1.yksus2_yksus_seq'::regclass))::text || '_'::text),
  nimetus character(70),
  "timestamp" character(14) DEFAULT to_char(now(), 'YYYYMMDDHH24MISS'::text),
  osakond character(10),
  username character(10) DEFAULT "current_user"(),
  klient character(40),
  superinden character(20),
  telefon character(10),
  aadress character(50),
  tlnr character(15),
  rus character(60),
  CONSTRAINT yksus2_pkey PRIMARY KEY (yksus),
  CONSTRAINT yksus2_osakond_fkey FOREIGN KEY (osakond)
      REFERENCES yksus2 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
);
ALTER TABLE yksus2
  OWNER TO mydb_owner;

CREATE TRIGGER yksus2_trig
  BEFORE INSERT OR UPDATE OR DELETE
  ON yksus2
  FOR EACH STATEMENT
  EXECUTE PROCEDURE setlastchange();

user330315 · Accepted Answer · 2013-07-21 07:42:05Z

284

ALTER TABLE yksus1
    SET SCHEMA firma1;

More details in the manual: http://www.postgresql.org/docs/current/static/sql-altertable.html

Associated indexes, constraints, and sequences owned by table columns are moved as well.

Not sure about the trigger function though, but there is an equivalent ALTER FUNCTION .. SET SCHEMA ... as well.

answered Jul 21, 2013 at 7:42

user330315

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

7 Comments

Tim Over a year ago

Take care about name clashes though. If any of the tables is using the same names for indexes, constraints etc. as the destination schema, the query will fail.

Sauron Over a year ago

Bear in mind that tables are usually linked to sequences, and sequences normally have to be moved too.

user330315 Over a year ago

@Sauron: not if those columns are defined as serial or the now recommended identity columns, then the underlying sequence will be moved as well.

Brian Burns Over a year ago

If schema doesn't exist yet, CREATE SCHEMA IF NOT EXISTS foo;

Eldhenn Over a year ago

SET SCHEMA works... almostly. You may have a field with "DEFAULT nextval(oldschema.seq_name)", or any other "DEFAULT old_schema.func_name()"; That will not be transformed, and will live until old schema is alive.

|

Kishore Uppala · Accepted Answer · 2024-03-27 14:05:28Z

The below query generates statements to move tables from the "public" schema to "new_schema".

Update source schema(here: public) and target schema(here: new_schema) accordingly.

SELECT 'ALTER TABLE ' || quote_ident(schemaname) || '.' 
|| quote_ident(tablename) || ' SET SCHEMA new_schema;'
FROM pg_tables
WHERE schemaname = 'public';

Sample Output:

+--------------------------------------------------+
|                     ?column?                     |
+--------------------------------------------------+
| ALTER TABLE public.error SET SCHEMA new_schema;  |
| ALTER TABLE public.error1 SET SCHEMA new_schema; |
| ALTER TABLE public.error2 SET SCHEMA new_schema; |
| ALTER TABLE public.test SET SCHEMA new_schema;   |
+--------------------------------------------------+

Collectives™ on Stack Overflow

how to move tables from public to other schema in Postgres

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related