PostgresSQL: Copy table from schemaA to schemaB in the same DB

Question

I have the following scenario, I have a schema called A and a schema called B. Both are in the same DB, both contain the same table, only in schema B it has no content, since I truncated the table with the TRUNCATE statement.

I have tried to use the pg_dump and pg_restore application with the following command.

pg_dump --file "table.sql" --username "user" --no-password --verbose --format=p --data-only --table "schemaA.tablename" "db"

Get a SQL script with the following statement:

COPY schemaA.tablename (column1, column2...) FROM STDIN;

//data

I have tried changing that

I have tried changing that COPY to schemaB but to no avail, the FROM STDIN statement I assume is why pg_restore takes the data through a file and there is the STDIN entry, couldn't I do it this way? Since making the changes does not work.

If this is not possible, the only thing I can think of is to do the following:

INSERT INTO schemaB.tablename
SELECT * FROM schemaA.tablename;

There is a lot of data, I don't know to what extent it is advisable to do so, what would be the best step to follow?

Thanks!!!

Answer 1

You cannot restore a pg_dump to a different schema.

Your INSERT INTO ... SELECT ... solution is good. An alternative could be to use COPY ... TO to write the contents of the first table to a file and COPY ... FROM to load it into the other table.