Whats is the best workaround to inherit unique contraints in Postgres?

Question

In Postgres I am trying to inherit an unique attribute from a parent to its children. The parent table is an abstract table, with no entries in it. The names of all children should be unique. Referring to the following little (stupid) example: There should be no apple with the same name as a banana (or of course other apple).

Minimalistic Postgres example for this scenario:

CREATE TABLE fruit(CONSTRAINT fruit_uniq_name UNIQUE (name))
CREATE TABLE banana(name text, length integer) INHERITS (fruit)
CREATE TABLE apple(name, diameter integer NOT NULL,) INHERITS (fruit)

After having read many posts on this problem. All of them came to the conclusion that this scenario is impossible to master only with Postgres' inheritance, I'd like to know if there is a best practice workaround, e.g. using triggers and functions, for this problem?

I would be very happy for every little snippet of code, that could help me out of this annoying trap.

Answer 1

I followed the advice of Laurenz Albe and I think I solved the problem by using triggers on the tables apple and banana and a trigger function tgf_name_exists() that tests the uniqueness.

This is the trigger function that tests for the uniqueness of the children names:

CREATE OR REPLACE FUNCTION tgf_name_exits()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    VOLATILE
    COST 100
AS $BODY$
    declare
    count_apple integer;
    count_banana integer;
    name text;
    schema text;
    error_text text;
BEGIN
    -- Variables
    error_text = '';
    schema = TG_TABLE_SCHEMA; -- actual schema
    name = NEW.name; --- actual attribute name

    -- Check
    EXECUTE format('SELECT count(*) FROM %s.apple apl WHERE apl.name=%L', schema, name) INTO count_apple;
    EXECUTE format('SELECT count(*) FROM %s.banana ban WHERE ban.name=%L', schema, name) INTO count_banana;

    -- Info 
    RAISE NOTICE 'Schema: %', schema;
    RAISE NOTICE 'Name: %', name;
    RAISE NOTICE 'Count: %', count_apple;
    RAISE NOTICE 'Count: %', count_banana;

    IF count_apple > 0 OR count_banana > 0 THEN
        -- Name ist already used
        if count_apple > 0 then
            error_text = error_text || "apple "
        end if;
        if count_banana > 0 then
            error_text = error_text || "banana "
        end if;
        RAISE EXCEPTION 'Name % already existing in table %', name, error_text;
    ELSE
        -- Name is unused -> OK 
        RETURN NEW;
    END IF;
END;
$BODY$;

These are the triggers for the tables apple and banana

CREATE TRIGGER tg_apple_name_instert_update
    BEFORE INSERT OR UPDATE 
    ON apple
    FOR EACH ROW
    EXECUTE PROCEDURE tgf_name_exits();

CREATE TRIGGER tg_banana_name_uniq
    BEFORE INSERT OR UPDATE 
    ON banana
    FOR EACH ROW
    EXECUTE PROCEDURE tgf_name_exits();

It would be very kind if someone could check this. From here it looks like it works.