postgresql - export select query result using command

Question

As we can take a backup file of database using pg_dump command, similarly can we take backup of a select query result.

For example if i have a query select * from tablename; then i want to take backup result of the query that can be restored somewhere.

Eric · Accepted Answer · 2019-03-06 01:49:49Z

8

You can use something like

copy (select * from tablename) to 'path/to/file';

it will generate csv file with results very same manner as pg_dump does (in fact in plain mode it actually runs COPY commands)

update

and if you want DDL as well, you can

create table specname as select * from tablename

and then

pg_dump -s specname

Eric

2,76923 silver badges28 bronze badges

answered Oct 26, 2017 at 11:10

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1