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 ?
2
-
2"Moving" a table involves to not keep the original table - at least in my understanding of "moving".user330315– user3303152016-10-06 07:59:29 +00:00Commented Oct 6, 2016 at 7:59
-
1Possible duplicate of Copy a table (including indexes) in postgresKevin Brown-Silva– Kevin Brown-Silva2016-10-09 19:53:38 +00:00Commented Oct 9, 2016 at 19:53
Add a comment
|
6 Answers
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;
3 Comments
Hrvoje
It didn't work unless quotation where included:
create table new_schema."new_one" (like old_schema."old_table" including all)"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
Mazimia
@a_horse_with_no_name this query doesn't copy the foreign keys. It does copy the unique constraints, however.
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;
4 Comments
MANU
@a_horse_with_no_name Yes.. good point.. That's there..... My mistake...
Pedro Cavalcante
If you do this, will the data in
schema2.the_table update automatically in response to a change in schema1.the_table?
Benji York
No, Pedro, the copy will be static.
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
Comments
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.
Comments
If you use PGAdmin:
- select the table you want to copy, select the SQL tab and copy the
CREATEscript, which include all keys and constraint - add the desired schema in front of the table name and execute it.
- execute
INSERT INTO schema2.the_table SELECT * FROM schema1.the_table;. Here you could encounter the errorcolumn "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;
