Postgresql INSERT ... ON CONFLICT ... WHERE not triggering

Question

I have a table like this:

CREATE TABLE import_files (
    id          SERIAL PRIMARY KEY,
    file        TEXT NOT NULL,
    volume      TEXT NOT NULL,
    lastimport  DATE DEFAULT CURRENT_DATE,
    UNIQUE (file,volume)
)

I am trying to import data into the table, such that if a combination of file and volume does not already exist in the table, a new row should be added, otherwise if there is an existing row, that row should be updated so that lastimport is today's date.

However I do not want to do any update if that row is already todays date (the same rows may be presented many times a day).

I am trying to use Postgresql 9.6's new ON CONFLICT clause for this, as follows:

INSERT INTO import_files(file,volume) 
VALUES('MyFile1', 'VolA')
ON CONFLICT (file,volume) DO
    UPDATE SET lastimport = CURRENT_DATE
WHERE EXCLUDED.lastimport < CURRENT_DATE
RETURNING id

but it does not seem to be working for me. Initial import works fine, but for the update case it does not seem to update even where the WHERE clause is true:

t1=> select * from import_files;
 id |  file   | volume | lastimport
----+---------+--------+------------
  1 | MyFile1 | VolA   | 2017-05-07
(1 row)

t1=>     INSERT INTO import_files(file,volume)
    VALUES('MyFile1', 'VolA')
    ON CONFLICT (file,volume) DO
        UPDATE SET lastimport = CURRENT_DATE
    WHERE EXCLUDED.lastimport < CURRENT_DATE
    RETURNING id
;
 id
----
(0 rows)

INSERT 0 0

Since today is 2017-05-08 I would have expected that row to be updated.

Have I misunderstood the purpose or usage of the WHERE clause in this context?

harmic · Accepted Answer · 2017-05-08 06:24:15Z

3

The solution is embarrassingly simple ...

The EXCLUDED table contains the rows originally proposed for insertion, not the current rows that are being updated. I was not specifying a value for lastimport so it would have been populated with the default value - in this case that was CURRENT_DATE, so the where clause would never be true.

Instead I should have been using this:

t1=> INSERT INTO import_files(file,volume)
    VALUES('MyFile1', 'VolA')
    ON CONFLICT (file,volume) DO
        UPDATE SET lastimport = CURRENT_DATE
    WHERE import_files.lastimport < CURRENT_DATE
    RETURNING id
;
 id
----
  1
(1 row)

INSERT 0 1

The where clause should have been referencing the current row values with import_files.lastimport instead of the proposed values with EXCLUDED.lastimport.

answered May 8, 2017 at 6:24

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Renzo · Accepted Answer · 2017-05-08 06:26:23Z

1

You could use a different approach that works on previous versions of PostgreSQL:

do $$
begin 
  insert into import_files (file,volume) values('MyFile1', 'VolA');
exception when unique_violation then
  update import_files 
  set lastimport = current_date 
  where file = 'MyFile1' and volume = 'VolA' and lastimport < current_date
end $$;

Note that this approach works only if there are no deletions of rows of the table.

answered May 8, 2017 at 6:26

Renzo

27.6k5 gold badges54 silver badges64 bronze badges

Collectives™ on Stack Overflow

Postgresql INSERT ... ON CONFLICT ... WHERE not triggering

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related