Let's say I have a table I need to update with a mixture of new, changed and unchanged existing data (for legacy reasons we cannot separate them into 3 different calls).

The minimal example table is this:

(
    id        integer,
    name      text
        constraint name_unique
            unique,
    metadata  jsonb,
    version   integer default 0
);

There are 3 different things that need to happen:

1. Insert the new values if name is not there (INSERT ... ON CONFLICT (name) ...)
2. Update metadata and bump version with 1 if name is already there and OLD.metadata <> NEW.metadata <- this is the one I cannot write down with the ON CONFLICT syntax as it only(?) gives me access to NEW value as EXCLUDED but not the value at hand in ROW.
3. Skip update and version bump if OLD.metadata = NEW.metadata

My current best guess was casting jsonb to plain text and see if the two was the same, but I could not figure out if it was possible to do it without having to write a FUNCTION and a TRIGGER.

Can these 3 things performed in one query?

CodePudding user response：

You can reference the new row version with EXCLUDED in the ON CONFLICT ... DO UPDATE clause:

INSERT INTO tab (name, metadata)
VALUES ('obadja', '{"silly": 42}')
ON CONFLICT ON name_unique
DO UPDATE SET metadata = EXCLUDED.metadata,
              version = tab.version   1
WHERE tab.metadata IS DISTINCT FROM EXCLUDED.metadata;

CodePudding user response：

I think I have solved this problem.

INSERT
INTO example (id, name, metadata, version)
VALUES (DEFAULT, 'Random', '{}', DEFAULT)
on conflict(name) do
    update set
               metadata = excluded.metadata,
               version = example.version   1
where
        example.name = excluded.name and
        cast(example.metadata as text) <> cast(excluded.metadata as text);

Let me know if I made any mistakes.