3

Given the following table:

create table some_table
(
    id                 uuid      default uuid_generate_v4() not null,
    last_modified_date timestamp default now()              not null,
    foreign_key        text,
    start              integer,
    end                integer,
    constraint some_table_pkey
        primary key (id),
    constraint some_table_foreign_key_fkey
        foreign key (foreign_key) references foreign_table
);

Then I added the following constraints:

CREATE INDEX some_table_foreign_key_idx
    ON some_table (foreign_key);

ALTER table some_table
    ADD CONSTRAINT some_table_start_check
        check (start > 0);

ALTER TABLE some_table
    add CONSTRAINT some_table_check
        check (end >= start);

ALTER TABLE some_table
    ADD CONSTRAINT unique_interval_to_foreign_key_constraint
        EXCLUDE USING gist
        (foreign_key WITH =,
         int4range(start, end, '[]') WITH &&
        );

Generating a table like so:

+--------------------------------------+-------+-----+--------------------+--------------------------------------+
| id                                   | start | end | last_modified_date | foreign_key                          |
+--------------------------------------+-------+-----+--------------------+--------------------------------------+
| 04ef8258-917c-46d6-8db4-9c704d3f4fbd | 10    | 20  | timestampA         | 04ef8258-917c-46d6-8db3-9c704d3f4fbd |
+--------------------------------------+-------+-----+--------------------+--------------------------------------+
| 04ef8258-917c-46d6-8db5-9c704d3f4fbd | 40    | 60  | timestampB         | 04ef8258-917c-46d6-8db3-9c704d3f4fbd |
+--------------------------------------+-------+-----+--------------------+--------------------------------------+

How do I INSERT a record into this table?

What have I tried?

INSERT INTO some_table (foreign_key, start, end) 
VALUES (`04ef8258-917c-46d6-8db3-9c704d3f4fbd`,  70, 80) 
ON CONFLICT (foreign_key, start, end) 
   DO UPDATE SET last_modified_date=NOW() RETURNING id;

I get the following error:

UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name

Raqib
  • 1,367
  • 11
  • 24

0 Answers0