CODEWITHSUNDEEP
sqlpostgresqlupsert
Sardorkhuja Tukhtakhodjayev
Sardorkhuja Tukhtakhodjayev

Reputation: 83

How to add comparing to ON CONFLICT () DO UPDATE

I need to check, if in table there are any operations with current user for today. Usually I compare time in this way: timestamp > CURRENT_TIMESTAMP::date

Could you please help, how to do it in INSERT in ON CONFLICT () DO UDPATE?

        INSERT INTO table (login, smth, timestamp)
          VALUES ('username', 'smth', CURRENT_TIMESTAMP)
        ON CONFLICT (login, timestamp) DO UPDATE
          SET smth = 'smth'
              timestamp = CURRENT_TIMESTAMP

Here will be exactly comparing of timestamp, but I need to check, if it's for today, like above: timestamp > CURRENT_TIMESTAMP::date

Thanks!

Upvotes: 0

Views: 1867

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

If you want to store the timestamp but have a unique constraint on the date, then you can do that easily in the most recent versions of Postgres using a computed column. This requires adding a new column which is the date into the table:

create table t (
     login text,
     smth text,
     ts timestamp,
     ts_date date generated always as (ts::date) stored
);

And then creating a unique constraint:

create unique index unq_t_login_timestamp on t(login, ts_date);

Now you can use on conflict:

INSERT INTO t (login, smth, ts)
    VALUES ('username', 'smth', CURRENT_TIMESTAMP)
    ON CONFLICT (login, ts_date) DO UPDATE
          SET smth = 'smth',
              ts = CURRENT_TIMESTAMP;

Here is the code in a db<>fiddle.

EDIT:

It is better to eschew the computed column and just use:

create unique index unq_t_login_timestamp on t(login, (timestamp::date));

Upvotes: 1

etsuhisa
etsuhisa

Reputation: 1758

If you can use CTE, see here.

In case of your question, the query is like below: (However, I'm not clear what "timestamp > CURRENT_TIMESTAMP::date" means.)

with
"data"("w_login","w_smth","w_timestamp") as (
  select 'username2'::text, 'smth'::text, CURRENT_TIMESTAMP
),
"update" as (
  update "table" set ("smth","timestamp")=("w_smth","w_timestamp") from "data"
  where "login"="w_login" and "w_timestamp">CURRENT_TIMESTAMP::date
  returning *
)
insert into "table"
select * from "data"
where not exists (select * from "update");

DB Fiddle

Upvotes: 1

Related Questions