3

Using postgresql 9.5, assume I have a table:

CREATE TABLE t (
 id INT,
 primary TEXT,
 secondary TEXT
)

I want to guarantee that the union of values in primary and secondary does not contain duplicates. Is this doable via index/checks or do I have to go to a trigger?

For example,

INSERT INTO t(id, primary, secondary) VALUES(1, 'a', 'b');
INSERT INTO t(id, primary, secondary) VALUES(1, 'x', 'y');
INSERT INTO t(id, primary, secondary) VALUES(1, 'a', 'z');
INSERT INTO t(id, primary, secondary) VALUES(1, 'z', 'a');

The latter two INSERTs should fail.

  • Uhm, isn't that just displacing the problem? Now I have to make sure I don't improperly link the values. – alphadogg Sep 9 '16 at 14:15
  • Please clarify: should (a,b) and (a,z) be allowed together or not? – ypercubeᵀᴹ Sep 9 '16 at 14:28
  • To simplify, if you only have {1,'a','b'} in the table, you should not be able to insert {1,'a','t'}, {1,'t','a'}, {1,'b','t'}, {1,'t','b'}. The id column is irrelevant. – alphadogg Sep 9 '16 at 14:40
  • I think this would be possible with an exclusion constraint if there was an access method for arrays exclude using gist ( (array["primary", secondary]) with &&) - but unfortunately there is no access method for that. – a_horse_with_no_name Sep 10 '16 at 7:01
3

A trigger solution might work but I'd prefer to normalize the design and have simple constraints:

The table that is now:

CREATE TABLE t (
 id INT PRIMARY KEY,
 t_primary TEXT,
 t_secondary TEXT
) ;

would become:

CREATE TABLE tt (
 id INT,
 t_no SMALLINT NOT NULL,
 t_text TEXT,
 PRIMARY KEY (id, t_no)
 UNIQUE (t_text),
 CHECK (t_no IN (1,2))
) ;

Then your inserts

INSERT INTO t (id, t_primary, t_secondary) VALUES (1, 'a', 'b');
INSERT INTO t (id, t_primary, t_secondary) VALUES (2, 'x', 'y');
INSERT INTO t (id, t_primary, t_secondary) VALUES (3, 'a', 'z');
INSERT INTO t (id, t_primary, t_secondary) VALUES (4, 'z', 'a');

would become:

INSERT INTO tt (id, t_no, t_text) VALUES (1, 1, 'a'), (1, 2, 'b');
INSERT INTO tt (id, t_no, t_text) VALUES (2, 1, 'x'), (2, 2, 'y');
INSERT INTO tt (id, t_no, t_text) VALUES (3, 1, 'a'), (3, 2, 'z');  -- will fail
INSERT INTO tt (id, t_no, t_text) VALUES (4, 1, 'z'), (4, 2, 'a');  -- will fail

Finally you can have the original output with:

CREATE VIEW t AS
SELECT 
    a.id, 
    a.t_text as t_primary,
    b.t_text as t_secondary,
FROM tt AS a
  JOIN tt AS b
    ON a.id = b.id AND a.t_no = 1 AND b.t_no = 2 ;

SELECT *
FROM t ;
1

A naive solution is to create unique indexes as:

CREATE TABLE t (
    x int,
    y int
);

CREATE UNIQUE INDEX X1 ON T (LEAST(x, y));
CREATE UNIQUE INDEX X2 ON T (GREATEST(x, y));

But as @alphadogg points out, this will fail in the following situation:

INSERT INTO t(x,y) VALUES (1,3); 
INSERT INTO t(x,y) VALUES (0,1);

1<3 and 1>0, so these tuples will not violate the unique indexes.

A kludge (that probably will perform badly for heavy modification of the table) is:

CREATE MATERIALIZED VIEW V(x) as 
    select x from t 
    union all 
    select y from t 
with data;

CREATE UNIQUE INDEX X1 ON V(x);

create or replace function refresh_mat_view()
returns trigger language plpgsql
as $$
begin
    refresh materialized view v; --
    return null; --
end $$;

create trigger refresh_mat_view
after insert or update on t 
for each row
    execute procedure refresh_mat_view();

Whenever a row is inserted or updated in t, x union all y is checked for uniqueness.

Test in sqlfiddle for PostgreSQL 9.3:

INSERT INTO t(x,y) VALUES (1,3), (0,1);
ERROR: could not create unique index "x1" Detail: Key (x)=(1) is duplicated. Where: SQL statement "refresh materialized view 

I'm no expert in PostgreSQL but I assume this is going to be very expensive for a large table with massive modifications.

Another option is to have an before trigger that checks for existence of new x and y in table, and raises an exception if found (I assume this is what @ypercubeᵀᴹ refers to at the beginning of his post). Personally I find triggers to be rather awkward in PostgreSQL so I won't try to create one (I'm rather surprised that I managed to create one above :-).

  • Doesn't work. INSERT INTO t(id,p,s) VALUES (1,'a','b'); then INSERT INTO t(id,p,s) VALUES (1,'t','b'); was allowed. After first insert, X1 contains 'a' and X2 contains 'b'. The second insert asks i X1 could contain 'b', which it can, and if X2 can contain 't', which it can. Thus allowing an unwanted record. – alphadogg Sep 10 '16 at 0:53
  • I understand that it could be done by normalizing, but wanted to know i I could do it without incurring join. – alphadogg Sep 10 '16 at 0:54
  • Ah yes, you're right. Thanks for pointing that out. I'll see if I can come up with an alternative approach or remove my answer. – Lennart Sep 10 '16 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.