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

Question

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 ?

"Moving" a table involves to **not** keep the original table - at least in my understanding of "moving". — , Oct 06 '16 at 07:59
Possible duplicate of [Copy a table (including indexes) in postgres](http://stackoverflow.com/questions/198141/copy-a-table-including-indexes-in-postgres) — Kevin Brown-Silva, Oct 09 '16 at 19:53

score 117 · Accepted Answer · answered Oct 06 '16 at 07:57

117

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;

answered Oct 06 '16 at 07:57

It didn't work unless quotation where included: `create table new_schema."new_one" (like old_schema."old_table" including all)` – Hrvoje Nov 10 '21 at 11:57
2

`"old_table"` is exactly the same name as `old_table` if you did need the quotes you created the tables with mixed cases and double quotes which is [strongly discouraged](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names) – Nov 10 '21 at 12:09
@a_horse_with_no_name this query doesn't copy the foreign keys. It does copy the unique constraints, however. – Mazimia Jul 17 '23 at 11:34

score 49 · Answer 2 · answered Sep 11 '17 at 14:06

49

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;

answered Sep 11 '17 at 14:06

Alec

630
5
8

26

@MANU: but it does not copy constraints and indexes – Sep 12 '17 at 08:02
4

@a_horse_with_no_name Yes.. good point.. That's there..... My mistake... – MANU Sep 12 '17 at 10:49
1

If you do this, will the data in `schema2.the_table` update automatically in response to a change in `schema1.the_table`? – Pedro Cavalcante Jun 13 '22 at 19:13
No, Pedro, the copy will be static. – Benji York Jan 23 '23 at 16:28

score 6 · Answer 3 · answered Oct 30 '20 at 04:46

6

Simple syntax that works as of v12:

CREATE TABLE newSchema.newTable
AS TABLE oldSchema.oldTable;

answered Oct 30 '20 at 04:46

Kyouma

320
6
14

2

This one does not copy constraints and indices, either. – dirkt Jan 21 '21 at 10:15

score 0 · Answer 4 · answered Apr 11 '23 at 18:25

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

score 0 · Answer 5 · answered Jun 08 '23 at 22:28

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.

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

5 Answers5