Use system timestamp comparison in SELECT query I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in getting my query correct, below is the query I am using but does not seem to work:

psql -d my_db -c 'copy (select * from mytab WHERE date_trunc('day',NOW() - interval '1 day') TO STDOUT' -o data1.copy;
The WHERE expression does not make sense:
WHERE date_trunc('day',NOW() - interval '1 day'

It has to evaluate to a boolean value and you need to compare to an actual column of the table like (assuming a timestamp , timestamptz or date column):
WHERE my_timestamp_column &gt;= (NOW()::date - 1)
AND   my_timestamp_column &lt;   NOW()::date

Postgres does not store a creation timestamp for table rows automatically. You need to add a column yourself to keep track (with default value and / or a trigger to make sure). Detailed instructions with code in this related answer:

Tables accessed during last period

&quot;Commit timestamps&quot; in Postgres 9.5 or later
You can activate the track_commit_timestamp setting to start tracking. Then you can use pg_xact_commit_timestamp(xmin) to retrieve the timestamp when each row was inserted or updated (after you started tracking). To get all rows that were inserted or updated within the last day:
SELECT * FROM mytab 
WHERE  pg_xact_commit_timestamp(xmin) &gt; now() - interval '1 day'

Certain limitations apply. Commit timestamps are not kept indefinitely. Read the Postgres Wiki for details.
Once activated, commit timestamps are tracked for all transactions in the DB cluster, which causes a performance hit. If you only need it for one or a few tables, rather add timestamps to involved table(s) manually like instructed above.