PostgreSQL 9.5 UPSERT in rule

Question

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)
);

JGH · Accepted Answer · 2017-06-21T12:12:24.277

2

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,''));

edited Jun 21 '17 at 12:12

answered Jun 20 '17 at 09:57

JGH

15,928
4
31
48

1

I'll try that, and if it is the case, as I suspected it is my understanding of SQL that was lacking. Thanks – GuiOm Clair Jun 20 '17 at 10:06
It is not working. By any chance when checking for conflict, the check is done to all the constraints ? Including the Primary key one ? Because if it is the case, even if I use the multiple UNIQUE constraint it is adding a new row containing the exact same combination, but with another ID. My ID is an autoincrement SERIAL so it is generated. – GuiOm Clair Jun 20 '17 at 11:49
I tried to add the columns I want to be checked, but still a new row is inserted anyway ON CONFLICT (name, code, district) DO NOTHING... – GuiOm Clair Jun 20 '17 at 12:10
1

Have you updated the table with the unique constraint? Please post the updated table definition. – JGH Jun 20 '17 at 12:31
see http://rextester.com/LDJA31246 , the row is not inserted the 2nd time. Make sure to add the missing closing parenthesis at the end of the rule. – JGH Jun 20 '17 at 13:06
I know where the problem is, I never have all the fields containing datas since I have a district information only when I don't have a street name (this is out of my hands). Do you know a way to deal with NULL values in that case? Is using an empty string like '' the only solution or is there a condition that could be used? Thanks a lot for your time anyway. – GuiOm Clair Jun 21 '17 at 07:10
Ok in fact I found the solution for the more specific problem I mentionned in my previous comment. I just have to create two constraints, one with name and code, and the other one with district and code. Thanks again for your support. :) – GuiOm Clair Jun 21 '17 at 07:17
@GuiOmClair see edited answer for a single constraint solution – JGH Jun 21 '17 at 12:14
Thank you. The solution I told about in my previous comment, do you think it is a safe one ? – GuiOm Clair Jun 21 '17 at 12:37
:-) only if you are 100% sure that nobody will ever populate the 3 fields... so it is working now but likely not future-proof – JGH Jun 21 '17 at 12:55
One last question : is it equivalent to create a unique index and to create a unique constraint with the definition `(COALESCE(name,''), code, COALESCE(district,''))` ? – GuiOm Clair Jun 22 '17 at 07:31
1

http://flatiron.engineering/technology/2016/09/13/uniqueness-in-postgres.html https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index – JGH Jun 22 '17 at 11:30

score 0 · Answer 2 · answered Oct 20 '20 at 18:59

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;

PostgreSQL 9.5 UPSERT in rule

2 Answers2

In addition to @JGH's answer.