Unique index on expressions doesn't work like I expect

Question

Here is example of my problem: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ddb9cfd2da315ecf36cfffd66853f023

I use this function:

CREATE OR REPLACE FUNCTION inListExistOrNull(list jsonb) RETURNS boolean AS
$BODY$
    DECLARE
        r TEXT;
        i boolean := false;
        vcount int;
    BEGIN
        FOR r IN SELECT * FROM jsonb_array_elements($1) LOOP
            vcount := (SELECT COUNT(*)
                       FROM table_example
                       WHERE data->>'test' LIKE '%' || r || '%');

            i := vcount > 0;

            IF i = true THEN
                EXIT;
            END IF;
        END LOOP;

        IF i = true THEN
            RETURN true;
        END IF;

        RETURN NULL;
    END;
$BODY$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;

I create a table and indexes like this:

CREATE TABLE IF NOT EXISTS table_example(
   id bigserial primary key,
   data jsonb,
   txid bigint
);

CREATE INDEX IF NOT EXISTS table_example_txid_index
   ON table_example(txid);

CREATE UNIQUE INDEX IF NOT EXISTS unique_table_example
   ON table_example(inListExistOrNull(data->'test'));

I insert some rows:

INSERT INTO table_example (id, data, txid)
   VALUES (1, '{"test": ["https://example.com/test/123", "https://example.com/test/678"]}', 1);
INSERT INTO table_example (id, data, txid)
   VALUES (2, '{"test": ["https://example.com/test/b4b81fb221d4fa641", "https://example.com/test/624f3e10048245fb1"]}', 2);
INSERT INTO table_example (id, data, txid)
   VALUES (4, '{"test": ["https://example.com/test/ggg", "https://example.com/test/hhh"]}', 4);
INSERT INTO table_example (id, data, txid)
   VALUES (5, '{"test": ["https://example.com/test/ggg"]}', 5);

I have no idea why I can create a row with id = 5. It should be caught by the unique index but it isn't.

This works like I'd expect:

INSERT INTO table_example (id, data, txid)
   VALUES (6, '{"test": ["https://example.com/test/b4b81fb221d4fa641", "https://example.com/test/624f3e10048245fb1"]}', 6);
ERROR:  duplicate key value violates unique constraint "unique_table_example"
DETAIL:  Key (inlistexistornull(data -> 'test'::text))=(t) already exists.

SOLUTIONS

TRIGGER:

CREATE OR REPLACE FUNCTION inListExistOrNull() RETURNS TRIGGER AS
$BODY$
    DECLARE
        r TEXT;
        i boolean := false;
        vcount int;
        newData jsonb;
    BEGIN
        newData := NEW.data->'test';

        FOR r IN SELECT * FROM jsonb_array_elements(newData) LOOP
            vcount := (SELECT COUNT(*) FROM table_example WHERE data->>'test' LIKE '%' || r || '%');

            i := vcount > 0;

            IF i = true THEN
                RAISE 'Duplicate data: %', r USING ERRCODE = '23505';
            END IF;
        END LOOP;

        RETURN NEW;
    END;
$BODY$
LANGUAGE 'plpgsql'
STABLE;

CREATE TRIGGER inListExistOrNullTrigger
BEFORE INSERT OR UPDATE ON table_example
    FOR EACH ROW EXECUTE PROCEDURE inListExistOrNull();

UNIQUE INDEX:

CREATE OR REPLACE FUNCTION inListExistOrNull(list jsonb) RETURNS int AS
$BODY$
    DECLARE
        r TEXT;
        i boolean := false;
        vcount int;
    BEGIN
        FOR r IN SELECT * FROM jsonb_array_elements($1) LOOP
            vcount := (SELECT COUNT(*) FROM table_example WHERE data->>'test' LIKE '%' || r || '%');

            i := vcount > 0;

            IF i = true THEN
                RAISE 'Duplicate data: %', r USING ERRCODE = '23505';
            END IF;
        END LOOP;

        IF i = true
        THEN
            RETURN true;
        ELSE
            RETURN NULL;
        END IF;
    END;
$BODY$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE UNIQUE INDEX IF NOT EXISTS unique_table_example ON table_example(inListExistOrNull(data->'test'));

Answer 1

One problem is that your function is not really immutable, and you are lying to PostgreSQL when you label it as such. IMMUTABLE means that it always has to return the same result for the same arguments, no matter what data are in the database and how it is configured.

The other problem is that the index won't do what you want it to: It will complain only the second time you insert a value where the function result is TRUE. That is what you experience here.

Instead of an index, you could use an AFTER INSERT OR UPDATE trigger with a similar function (raising an exception if duplicates are encountered), which will do what you want. Then you can label the function STABLE as you should, and everything should work fine.