How to add comparing to ON CONFLICT () DO UPDATE

Question

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!

Is that `=` an assignment or a comparison? – jarlh Aug 26 '20 at 09:04 — jarlh, Aug 26 '20 at 09:04
in last and previous lines? That's assignment – Sardorkhuja Tukhtakhodjayev Aug 26 '20 at 09:06 — Sardorkhuja Tukhtakhodjayev, Aug 26 '20 at 09:06

Gordon Linoff · Accepted Answer · 2020-08-26T14:31:16.603

1

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));

edited Aug 26 '20 at 14:31

answered Aug 26 '20 at 10:49

Gordon Linoff

1,242,037
58
646
786

Thanks you! But I guess, that's not what I'm looking exactly. I need to compare time nor in ms and not even in seconds, but in days. But I have to save in DB timestamp in ms (not data-format). I hope, I could now explain, what I wanted – Sardorkhuja Tukhtakhodjayev Aug 26 '20 at 11:20
@SardorkhujaTukhtakhodjayev . . . This does do the comparison in days and stores the value as a complete timestamp. Did you try running the code? The SQL Fiddle shows that the value changes. – Gordon Linoff Aug 26 '20 at 12:13
You can don't really need the computed column, so this would also be possible with older Postgres versions: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e027d16a060a1a93099849e5209cedd3 – Aug 26 '20 at 14:09
@a_horse_with_no_name . . . I thought that was possible, but when I tried, I got a syntax error. I must've had some other typo. One of the hazards of actually testing code. – Gordon Linoff Aug 26 '20 at 14:30

score 1 · Answer 2 · answered Aug 26 '20 at 12:14

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

How to add comparing to ON CONFLICT () DO UPDATE

2 Answers2