How to give WHERE condition in pg_dump?

Question

How to give WHERE condition in pg_dump to extract for particular values in below command,

pg_dump --table=export_table --data-only --column-inserts my_database > data.sql

Craig

pg_dump dumps databases; if you want to extract data use a query. – Pipage

This question makes no sense. pg_dump is a command line backup tool. WHERE is a clause in the SQL language. Clarify your objective, please. And always disclose your version of Postgres. – Flirtation

Answer 1

Postgres does not have any -- flag to filter the data you want to extract from a --table.

The best way you can do this is probably to select the data you want to extract with a query and then to save it into a csv.

then you can export that csv wherever you want and import the data back into a table having the same structure of the source table.

e.g.

let's suppose the data you want to dump are selected by the following query:

select * from my_table where date>=current_date-7;

access the source database and save tha desired data into a csv

\COPY (select * from my_table where date>=current_date-7) TO '~/my_local_path/dump_mydb_7daysdata.csv';

transfer the csv file (if you wish to)

scp ~/my_local_path/dump_mydb_7daysdata.csv <user>@<ip>:~/my_path_2/

access another database and import the data from the csv file

\COPY my_table_2 FROM '~/my_path_2/dump_mydb_7daysdata.csv';

Again, note that my_table and my_table_2 must have the same structure.

Addition:

If you want to define the delimiter and add the header

copy to

\COPY (select * from my_table where date>=current_date-7) TO '~/my_local_path/dump_mydb_7daysdata.csv' with delimiter ';' CSV HEADER;

copy from

\COPY my_table_2 FROM '~/my_path_2/dump_mydb_7daysdata.csv' with delimiter ';' CSV HEADER;

Alvita

> with the same structure of the source table; does this command handle the creation of the table with the same structure when you restore it as CSV? – Walhalla

@Walhalla no, it only uploads the data into an already exisitng table on the destination DB, having the same structure of the table in the source DB. – Alvita