Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE test_table (
    a integer,
    b integer,
    c integer
);
INSERT INTO test_table (a, b, c) VALUES
(1, 2, 22),
(1, 2, 22),
(1, 2, 22),
(1, 3, 34),
(2, 3, 26),
(2, 3, 26)
returning *;

CREATE TABLE




a
b
c


1
2
22

1
2
22

1
2
22

1
3
34

2
3
26

2
3
26



INSERT 0 6


CREATE OR REPLACE FUNCTION check_conditional_unique() RETURNS TRIGGER AS $f$
BEGIN
    IF EXISTS (
        SELECT FROM test_table t2
        WHERE (t2.a,t2.b) = (NEW.a,NEW.b) 
        AND    t2.c       <> NEW.c
    ) THEN
        RAISE EXCEPTION 'New values in c for the same a and b combination.';
    END IF;
    IF EXISTS (
        SELECT FROM test_table t2
        WHERE (t2.a,t2.b) <> (NEW.a,NEW.b) 
        AND    t2.c       =   NEW.c
    ) THEN
        RAISE EXCEPTION 'Duplicate values in c for different a and b combination.';
    END IF;
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_conditional_unique
BEFORE INSERT OR UPDATE ON test_table
FOR EACH ROW
EXECUTE FUNCTION check_conditional_unique();

CREATE FUNCTION



CREATE TRIGGER


--should be rejected
INSERT INTO test_table (a, b, c) VALUES
(1,2,23)
returning *;

ERROR:  New values in c for the same a and b combination.
CONTEXT:  PL/pgSQL function check_conditional_unique() line 8 at RAISE


--should be rejected
INSERT INTO test_table (a, b, c) VALUES
(2,3,34)
returning *;

ERROR:  New values in c for the same a and b combination.
CONTEXT:  PL/pgSQL function check_conditional_unique() line 8 at RAISE


--should be rejected
INSERT INTO test_table (a, b, c) VALUES
(7,9,22)
returning *;

ERROR:  Duplicate values in c for different a and b combination.
CONTEXT:  PL/pgSQL function check_conditional_unique() line 15 at RAISE