UPSERT a row depending on the unique combination of values of two columns

Question

I have the following table in a database:

CREATE TYPE status AS ENUM (
    'void',
    'steady',
    'transition'
);

CREATE TABLE relations (
    marker integer NOT NULL,
    related integer[] NOT NULL,
    status status DEFAULT 'void'::status NOT NULL,
    id serial -- pgAdmin requires primary key
);

ALTER TABLE ONLY relations
    ADD CONSTRAINT pkey_id PRIMARY KEY (id);

INSERT INTO relations (marker, related, status)
VALUES
(3, '{6}', 'steady'::status),
(3, '{2}', 'transition'::status),
(6, '{4}', 'void'::status),
(6, '{2}', 'steady'::status),
(4, '{2}', 'steady'::status),
(4, '{6}', 'void'::status);

This is what the table looks like:

 marker | related |   status   | id 
--------+---------+------------+----
      3 | {6}     | steady     |  1
      3 | {2}     | transition |  2
      6 | {4}     | void       |  3
      6 | {2}     | steady     |  4
      4 | {2}     | steady     |  5
      4 | {6}     | void       |  6

The marker/status combinations are supposed to be unique, even though there is no corresponding constraint, yet. This is not the issue here. I also have this function:

CREATE OR REPLACE FUNCTION update_relations(integer, integer, status) RETURNS void
    LANGUAGE plpgsql
    AS $_$
BEGIN
    update relations
        set related = array_append(related,
        (CASE
            WHEN marker = $1 THEN $2
            WHEN marker = $2 THEN $1
        END)
        )
    where
        marker in ($1,$2) AND
        status = $3;
END;
$_$;

When I run

SELECT update_relations(3, 4, 'void'::status);

then I want the row which marker value is 4 AND status is 'void' to update its related value and append a 3 to the array. Accordingly, the row that has marker = 3 and status = 'void'::status shall append a 4 to its related array. However, this is the result:

 marker | related |   status   | id 
--------+---------+------------+----
      3 | {6}     | steady     |  1
      3 | {2}     | transition |  2
      6 | {4}     | void       |  3
      6 | {2}     | steady     |  4
      4 | {2}     | steady     |  5
      4 | {6,3}   | void       |  6

As you can see, the row with marker = 4 and status = 'void'::status is updated as intended. Since there is no corresponding row that meets the requirement for 'marker = 3 and status = 'void'::status` no update happens. In such a case I would like to insert a row, so that the result would be:

 marker | related |   status   | id 
--------+---------+------------+----
      3 | {6}     | steady     |  1
      3 | {2}     | transition |  2
      6 | {4}     | void       |  3
      6 | {2}     | steady     |  4
      4 | {2}     | steady     |  5
      4 | {6,3}   | void       |  6
      3 | {4}     | void       |  7

How can I UPSERT the table, if a required marker/status combination is not present?

PS: I am using postgres 9.4.

Andomar · Accepted Answer · 2018-06-09T20:06:29.480

The Postgres "upsert" function ON CONFLICT UPDATE is for 9.5 and higher, but you're on 9.4.

If you're not worried about concurrency, you can do the simple:

if exists (select * from relations where marker in ($1,$2) AND status = $3) then
    update  relations
    set     ...
    where   marker in ($1,$2) AND status = $3;
else
    insert  into relations
            (marker, related, status)
    values  ($1, ARRAY[$2], $3);
end if

This approach does have concurrency issues. See this question for better (and more complex) solutions.

UPSERT a row depending on the unique combination of values of two columns

1 Answers1