'How to copy an table from one database to another database in Postgres

I have a table tbl_a in schema sch_a in a database db_a I want to copy this table tbl_a to schema sch_b in database db_b. Any suggestion?

postgresql


Solution 1:[1]

You can use copy :

sudo -u postgres psql --dbname=db_a -c "\copy (SELECT * FROM sch_a.tbl_a ) TO '/path/`date +%Y-%m-%d`_sch_a_tbl_a.csv' CSV DELIMITER '|' HEADER"

Then import it like so :

sudo -u postgres psql --dbname=db_b -c "\copy sch_b.tbl_b FROM '/path/`date +%Y-%m-%d`_sch_a_tbl_a.csv' DELIMITER '|' CSV"

You can also use dblink

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Addell El-haddad

Related Questions