Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

246
Vistas
Postgres UPSERT with EXCLUSION CONSTRAINT

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

over 4 years ago · Santiago Trujillo
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda