CODEWITHSUNDEEP
postgresqlpsqlpgadmin
ams
ams

Reputation: 62712

How to export the result set of a postgres query in a format that is importable with psql?

Using psql is there a way to do a select statement where the output is a list of insert statements so that I can execute those insert statements somewhere else.

SELECT * FROM foo where some_fk=123;

Should output 

INSERT INTO foo 
(column1,column2,...) VALUES 
('abc','xyyz',...),
('aaa','cccc',...),
 .... ;

That I can redicet to a file say export.sql which I can then import with psql -f export.sql My goal is to move export the result of a select statement in a format that I can import into another database instance with exactly the same table structure.

Upvotes: 4

Views: 4574

Answers (1)

bma
bma

Reputation: 9796

Have a look at the --inserts option of pg_dump

pg_dump -t your_table --inserts -f somefile.txt your_db

Edit the resulting file if necessary.

For a subset, as IgorRomanchenko mentioned, you can use COPY with a SELECT statement.

Example of COPYing as CSV.

COPY (select * from table where foo='bar') TO '/path/to/file.csv' CSV HEADER

Upvotes: 3

Related Questions