SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create table t( user_id serial Primary Key, date_pay timestamp ); insert into t(date_pay) values(now() - interval '1 day '); insert into t(date_pay) values(now() - interval '5 day '); insert into t(date_pay) values(now() - interval '2 day '); insert into t(date_pay) values(now() - interval '5 day '); insert into t(date_pay) values(now() - interval '4 day '); insert into t(date_pay) values(now() - interval '3 day '); select * from t order by user_id; with t_check as ( select coalesce((select DATE_PART('day', (date_pay::timestamp - now()::timestamp))>=0 from t where user_id = 1 limit 1), true) as is_added ) INSERT INTO t (user_id, date_pay) VALUES (1, '2022-09-01 00:00:00') ON CONFLICT (user_id) DO UPDATE set date_pay = ( SELECT case when DATE_PART('day', (date_pay::timestamp - now()::timestamp)) <= 0 then date_pay + interval '31 day' else now() + interval '31 day' end FROM t WHERE user_id = 1) returning (select is_added from t_check limit 1);
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear