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;


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?


1 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.

  • Thanks for clarifying my doubts.
    – fah81
    Nov 21 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.