'PostgreSQL - Constraint with differents data types

In PostgreSQL, I need a constraint to control if a value(s) is/are exists. This constraint is applied in an uuid[] data type column. It must check if each values of the array exists in an other column of the same table, in a uuid data type column.

I'm able to write a constraint with two similar data types, but how to run the same logic with two differents data types ?

In my case: check values in an uuid[] array with an uuid type.

Table

CREATE TABLE IF NOT EXISTS public.contact
(
    uuid uuid NOT NULL DEFAULT uuid_generate_v4(),    
    name character varying COLLATE pg_catalog."default" NOT NULL,    
    parent_uuid uuid,
    parent_name character varying COLLATE pg_catalog."default",
    child_uuids uuid[],
    child_names character varying[],
    CONSTRAINT contact_pk PRIMARY KEY (uuid),
    CONSTRAINT name_uk UNIQUE (name)       
)

TABLESPACE pg_default;

First constraint (parent): Ok

-- Check if parent_uuid exists in public.contact
ALTER TABLE public.contact ADD CONSTRAINT parent_uuid_fk FOREIGN KEY (parent_uuid)
REFERENCES public.contact (uuid) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;

Second constraint (children): Incompatible types: uuid[] and uuid

-- Check if child_uuids exists in public.contact
ALTER TABLE public.contact ADD CONSTRAINT child_uuids_fk FOREIGN KEY (child_uuids)
REFERENCES public.contact (uuid) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source