CODEWITHSUNDEEP
postgresqlupsert
GuiOm Clair
GuiOm Clair

Reputation: 149

PostgreSQL 9.5 UPSERT in rule

I have an INSERT rule in an updatable view system, for which I would like to realize an UPSERT, such as :

CREATE OR REPLACE RULE _insert AS
ON INSERT TO vue_pays_gex.bals
DO INSTEAD (
INSERT INTO geo_pays_gex.voie(name, code, district) VALUES (new.name, new.code, new.district)
ON CONFLICT DO NOTHING;

But my since there can be many different combinations of these three columns, I don't think I can set a CONSTRAINT including them all (although I may be missing a point of understanding in the SQL logics), hence nullifying the ON CONFLIT DO NOTHING part.

The ideal solution would seem to be the use of an EXCEPT, but it only works in an INSERT INTO SELECT statement. Is there a way to use an INSERT INTO SELECT statement referring to the newly inserted row? Something like FROM new.bals (in my case)?

If not I could imagine a WHERE NOT EXISTS condition, but the same problem than before arises.

I'm guessing it is a rather common SQL need, but cannot find how to solve it. Any idea?

EDIT :

As requested, here is the table definition :

CREATE TABLE geo_pays_gex.voie
(
  id_voie serial NOT NULL,
  name character varying(50),
  code character varying(15),
  district character varying(50),
  CONSTRAINT prk_constraint_voie PRIMARY KEY (id_voie),
  CONSTRAINT voie_unique_key UNIQUE (name, code, district)
);

Upvotes: 1

Views: 557

Answers (2)

Evgeny Nozdrev
Evgeny Nozdrev

Reputation: 1566

In addition to @JGH's answer.

INSERT in rule for INSERT will lead to infinity recursion (Postgres 9.6).

Full (NOT)runnable example:

CREATE SCHEMA ttest;

CREATE TABLE ttest.table_1 (
  id bigserial
    CONSTRAINT pk_table_1 PRIMARY KEY,
  col_1 text,
  col_2 text
);

CREATE OR REPLACE RULE table_1_always_upsert AS
ON INSERT TO ttest.table_1
DO INSTEAD (
  INSERT INTO ttest.table_1(id, col_1, col_2)
    VALUES (new.id, new.col_1, new.col_2)
  ON CONFLICT ON CONSTRAINT pk_table_1
    DO UPDATE
    SET col_1 = new.col_1,
      col_2 = new.col_2
);

INSERT INTO ttest.table_1(id, col_1, col_2)  -- will result error: infinity recursion in rules
  VALUES (1, 'One', 'A'),
         (2, 'Two', 'B');

INSERT INTO ttest.table_1(id, col_1, col_2)
  VALUES (1, 'One_updated', 'A_updated'),
         (2, 'Two_updated', 'B_updated'),
         (3, 'Three_inserted', 'C_inserted');

SELECT *
FROM ttest.table_1;

Upvotes: 0

JGH
JGH

Reputation: 17906

How do you define uniqueness? If it is the combination of name + code + district, then just add a constraint UNIQUE(name, code, district) on the table geo_pays_gex.voie. The 3, together, must be unique... but you can have several time the same name, or code, or district.

See it at http://rextester.com/EWR73154

EDIT ***

Since you can have Nulls and want to treat them as a unique value, you can replace the constraint creation by a unique index that replace the nulls

CREATE UNIQUE INDEX
voie_uniq ON voie
(COALESCE(name,''), code, COALESCE(district,''));

Upvotes: 2

Related Questions