Re: Where clause in pg_dump: need help

Hello:

On Thu, Jul 7, 2016 at 4:06 PM, Prashanth Adiyodi
<Prashantha(at)celltick(dot)com> wrote:
> I am working on a script where I need to take backup of certain tables (or a database) using the “date_trunc('day', NOW() - interval '1 month');” as a where clause. I have read online that this is achievable with the “t” option and I have tried the below command
> pg_dump --table=re_userstatus --data-only --column-inserts -t "date_trunc('day', NOW() - interval '1 month')" comp_db > data1.sql
> but this does not work, could you please help with the correct syntax or redirect me to any documentation which implements this, I am a newbee to Postgres SQL and I need this help from you, my pg_dump version is

Copying any error message would have been nice. Anyway, AFAIK, -t is a
synonim for --table, and is not used for that.

I see you are trying to dump just one table, as a series of INSERT
commands. I, personally, would recommend to use a COPY using SQL for
that, something along the lines of

psql -c '\copy (select * from re_userstatus WHERE date_trunc('day',
NOW() - interval '1 month').... TO STDOUT' -o data1.copy

The command lets you put any query ( so you can archive
whatevercolumns you want / need )

And these files are easier to work with ( specially in the default tab
delimited format ), are smaller and can easily be transformed to a
INSERT sequence ( or COPY'ed back in in the same table or another one
).

Frnacisco Olarte.