CODEWITHSUNDEEP
postgresql
Richie
Richie

Reputation: 5189

POSTGRES mydate column + interval with configurable parameter syntax

I have the following query in Postgres that works for me.

DELETE 
FROM transaction_detail th
WHERE transaction_id in (select transaction_id from transaction_head where now < th.created_ts + interval '120' day);

I now want to change the hardcoded retention period of '120' to a configurable parameter.

i.e.

DELETE 
FROM transaction_detail td
WHERE td.transaction_id in (select th.id from transaction_head th where th.created_ts + (select retention_period from my_app_params) < now());

This does not work for me because I can't quite get the syntax right. Can someone help me with fixing the syntax of the second query please?

thanks

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just use multiplication:

DELETE FROM transaction_detail td
    WHERE td.transaction_id in (select th.id
                                from transaction_head th
                                where th.created_ts + (select retention_period from my_app_params) * interval '1 day' < now()
                               );

Upvotes: 1

Related Questions