Unanswered: copy tables from one schema to another in the same db
first post, so please bear with me.
I have a postgresql database with a postgis spatial extension to it. In that db I have one schema with about 120 tables. I now want to copy / move some of the tables to new schemas based on who have the rights to update the tables. How do I copy the relevant tables to the new schema? I´ve tried with the backup function in pgadmin (I guess its a GUI to pg_dump) from the source schema (only the data) and then using the restore to the destination schema without any luck. Can you guys point me in the right direction? It seems that the data is backed up (atleast it returns 0) but when I try to restore the data in the new schema the tables are not showing.
I am agree your point. We can create duplicate table with LIKE operator.
postgres=# set search_path to public;
List of relations
Schema | Name | Type | Owner
public | test | table | postgres
public | test3 | table | postgres
public | test4 | table | postgres
postgres=# create table test.test3 (like test3 including constraints);
postgres=# select * from test.test3;
CREATE TABLE new_schema.table1 (LIKE old_schema.table1 INCLUDING CONSTRAINTS INCLUDING INDEXES);
with that its possible to create a new table under the new schema with the structure and constraints, primary key etc. as the original one. The problem is to migrate the data from the orginal table to the new one (remember that the structure is the same so it shouldn´t be that difficult). I´ve tried with SELECT * INTO but that seems to try to create a new table which is already there. If I try to use the SELECT * INTO without having created a table I will only get the data and not the primary key etc.
I also have som sequences (data applied to the primary key). How do I copy that from the original schema to the new one?
Sorry if this is basic knowledge, but have looked in the documentation and on the web without any luck.