CODEWITHSUNDEEP
postgresqlpgadmin
MANU
MANU

Reputation: 1446

How to copy certain tables from one schema to another within same DB in Postgres keeping the original schema?

I want to copy only 4 tables from schema1 to schema2 within same DB in Postgres. And would like to keep the tables in schema1 as well. Any idea how to do that in pgadmin as well as from postgres console ?

Upvotes: 51

Views: 75312

Answers (6)

Emaborsa
Emaborsa

Reputation: 2880

If you use PGAdmin:

  1. select the table you want to copy, select the SQL tab and copy the CREATE script, which include all keys and constraint
  2. add the desired schema in front of the table name and execute it.
  3. execute INSERT INTO schema2.the_table SELECT * FROM schema1.the_table;. Here you could encounter the error column "your_column" is of type XX but expression is of type YY. In this case I have specified all columns: INSERT INTO schema2.the_table (fieldq, field2, ...fieldN) SELECT fieldq, field2, ...fieldN FROM schema1.the_table;

Upvotes: 0

Daniel Nalbach
Daniel Nalbach

Reputation: 1233

This will loop through all tables in the old schema and recreate them with data (no constraints, indexes, etc) in the new schema.

-- Set the search path to the target schema
SET search_path = newSchema;

-- Loop over the table names and recreate the tables
DO $$
DECLARE
  table_name text;
BEGIN
  FOR table_name IN
    SELECT t.table_name
    FROM information_schema.tables t
    WHERE t.table_schema = 'public'
      AND t.table_type = 'BASE TABLE'
  LOOP
    EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' AS TABLE oldSchema.' || quote_ident(table_name);
  END LOOP;
END $$;

This is especially useful for collapsing multiple schemas for data warehousing when you don't need all the extras attached to the tables and just want a clean copy of the intact data.

Upvotes: 0

Ricardo Mayerhofer
Ricardo Mayerhofer

Reputation: 2309

PG dump and PG restore are usually the most efficient tools.

From the command line:

pg_dump --dbname=mydb --schema=my_schema --file=/Users/my/file.dump --format=c --username=user --host=myhost --port=5432

pg_restore --dbname=mydb --schema=my_schema --format=c --username=user --host=myhost --port=5432 /Users/my/file.dump --no-owner

Upvotes: 1

Kyouma
Kyouma

Reputation: 410

Simple syntax that works as of v12:

CREATE TABLE newSchema.newTable
AS TABLE oldSchema.oldTable;

Upvotes: 6

Alec
Alec

Reputation: 640

You can use CREATE TABLE AS SELECT. This ways you do not need to insert. Table will be created with data.

CREATE TABLE schema2.the_table
AS 
SELECT * FROM schema1.the_table;

Upvotes: 50

user330315
user330315

Reputation:

You can use create table ... like 

create table schema2.the_table (like schema1.the_table including all);

Then insert the data from the source to the destination:

insert into schema2.the_table
select * 
from schema1.the_table;

Upvotes: 130

Related Questions