-- Need this cast, or the FK constraint definitions will be rejected:
CREATE FUNCTION varchar_to_uuid(VARCHAR) RETURNS uuid AS $$
SELECT uuid_in($1::cstring);
$$ LANGUAGE sql immutable;

CREATE CAST (VARCHAR AS UUID) WITH FUNCTION varchar_to_uuid(VARCHAR) AS IMPLICIT;

CREATE TABLE public.revisions
(
     id uuid NOT NULL,
     revisions_previous_id character varying COLLATE 
pg_catalog."default", --oops, should have used uuid here
     revisions_next_id character varying COLLATE pg_catalog."default", 
--same here..
     customer_notice character varying COLLATE pg_catalog."default",
     CONSTRAINT pk_revisions PRIMARY KEY (id),
     CONSTRAINT fk_revisions_next FOREIGN KEY (revisions_next_id)
         REFERENCES public.revisions (id) MATCH SIMPLE
         ON UPDATE CASCADE
         ON DELETE CASCADE,
     CONSTRAINT fk_revisions_previous FOREIGN KEY (revisions_previous_id)
         REFERENCES public.revisions (id) MATCH SIMPLE
         ON UPDATE CASCADE
         ON DELETE CASCADE
);

CREATE INDEX idx_fk_revisions_cart_revisions_next_id
     ON public.revisions USING btree
     (revisions_next_id COLLATE pg_catalog."default" ASC NULLS LAST)  
;

CREATE INDEX idx_fk_revisions_cart_revisions_previous_id
     ON public.revisions USING btree
     (revisions_previous_id COLLATE pg_catalog."default" ASC NULLS LAST) 
;

INSERT INTO public.revisions(
     id, revisions_previous_id, revisions_next_id, customer_notice)
     VALUES ('5c617ce7-688d-4bea-9d66-c0f0ebc635da', null, null, 'hi');

-- fails:
delete from revisions where id='5c617ce7-688d-4bea-9d66-c0f0ebc635da';