pgadmin4 Schema Diff Tool provides wrong diff for triggers

In both schemata i have defined exactly the same triggers, even taking care that in both schemata the trigger and function names are written using only lower-case letters, nevertheless schema diff states a trigger has to be dropped. see here:

The expected behavior would be that the diff is empty.

Sep 01 '25 08:09 tuerker

by the way the following query shows that both schemata have the same triggers since the query's result is empty:

WITH trigger_comparison AS (
    SELECT 
        trigger_name,
        event_object_table,
        action_timing,
        event_manipulation,
        action_statement,
        'schema1' as source_schema
    FROM information_schema.triggers 
    WHERE trigger_schema = 'schema1'
    
    UNION ALL
    
    SELECT 
        trigger_name,
        event_object_table,
        action_timing,
        event_manipulation,
        action_statement,
        'schema2' as source_schema
    FROM information_schema.triggers 
    WHERE trigger_schema = 'schema2'
)
SELECT trigger_name, COUNT(*) as count
FROM trigger_comparison
GROUP BY trigger_name, event_object_table, action_timing, event_manipulation, action_statement
HAVING COUNT(*) = 1;

Sep 01 '25 09:09 tuerker

Hi @tuerker Can you please provide the Source and target DDL.

Sep 05 '25 11:09 anilsahoo20

@anilsahoo20

here is an example:

source

CREATE TABLE public.sequencingapplication
(
    id bigint NOT NULL DEFAULT nextval('sequencingapplication_id_seq'::regclass),
    name character varying(256) COLLATE pg_catalog."default" NOT NULL,
    displayname character varying(290) COLLATE pg_catalog."default" GENERATED ALWAYS AS (displayname(id, name)) STORED,
    description character varying(256) COLLATE pg_catalog."default",
    enabled boolean NOT NULL DEFAULT true,
    servicetype_id bigint NOT NULL,
    sortposition bigint NOT NULL DEFAULT 0,
    created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    createdby character varying(32) COLLATE pg_catalog."default" NOT NULL DEFAULT 'admin'::character varying,
    modified timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modifiedby character varying(32) COLLATE pg_catalog."default" NOT NULL DEFAULT 'admin'::character varying,
    lockversion integer NOT NULL DEFAULT 0,
    entity_id bigint,
    CONSTRAINT sequencingapplication_pkey PRIMARY KEY (id),
    CONSTRAINT sequencingapplication_name_servicetype_id_key UNIQUE (name, servicetype_id)
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT sequencingapplication_entity_id_fkey FOREIGN KEY (entity_id)
        REFERENCES public.entity (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT sequencingapplication_servicetype_id_fkey FOREIGN KEY (servicetype_id)
        REFERENCES public.servicetype (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT sequencingapplication_name_check CHECK (name::text <> ''::text),
    CONSTRAINT sequencingapplication_createdby_check CHECK (createdby::text <> ''::text),
    CONSTRAINT sequencingapplication_modifiedby_check CHECK (modifiedby::text <> ''::text)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.sequencingapplication
    OWNER to benim;
CREATE INDEX sequencingapplication_created_year_idx
    ON public.sequencingapplication USING btree
    ((date_part('year'::text, created)::integer) ASC NULLS LAST)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_createdby_idx
    ON public.sequencingapplication USING gin
    (createdby COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_description_idx
    ON public.sequencingapplication USING gin
    (description COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_description_ts_idx
    ON public.sequencingapplication USING gin
    (to_tsvector('english'::regconfig, COALESCE(description)::text))
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_entity_id_idx
    ON public.sequencingapplication USING btree
    (entity_id ASC NULLS LAST)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_modifiedby_idx
    ON public.sequencingapplication USING gin
    (modifiedby COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_name_idx
    ON public.sequencingapplication USING gin
    (name COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_servicetype_id_idx
    ON public.sequencingapplication USING btree
    (servicetype_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE OR REPLACE TRIGGER update_servicetype_deletable_after_sequencingapplicationchange
    AFTER INSERT OR DELETE OR UPDATE OF servicetype_id
    ON public.sequencingapplication
    FOR EACH ROW
    EXECUTE FUNCTION public.update_servicetype_deletable();

target

CREATE TABLE public.sequencingapplication
(
    id bigint NOT NULL DEFAULT nextval('sequencingapplication_id_seq'::regclass),
    name character varying(256) COLLATE pg_catalog."default" NOT NULL,
    created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    createdby character varying(32) COLLATE pg_catalog."default" NOT NULL DEFAULT 'admin'::character varying,
    modified timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modifiedby character varying(32) COLLATE pg_catalog."default" NOT NULL DEFAULT 'admin'::character varying,
    lockversion integer NOT NULL DEFAULT 0,
    enabled boolean NOT NULL DEFAULT true,
    description character varying(256) COLLATE pg_catalog."default",
    servicetype_id bigint NOT NULL,
    sortposition bigint NOT NULL DEFAULT 0,
    displayname character varying(290) COLLATE pg_catalog."default" GENERATED ALWAYS AS (displayname(id, name)) STORED,
    entity_id bigint,
    CONSTRAINT sequencingapplication_pkey PRIMARY KEY (id),
    CONSTRAINT sequencingapplication_name_servicetype_id_key UNIQUE (name, servicetype_id)
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT sequencingapplication_entity_id_fkey FOREIGN KEY (entity_id)
        REFERENCES public.entity (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT sequencingapplication_servicetype_id_fkey FOREIGN KEY (servicetype_id)
        REFERENCES public.servicetype (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT sequencingapplication_createdby_check CHECK (createdby::text <> ''::text),
    CONSTRAINT sequencingapplication_modifiedby_check CHECK (modifiedby::text <> ''::text),
    CONSTRAINT sequencingapplication_name_check CHECK (name::text <> ''::text)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.sequencingapplication
    OWNER to benim;
CREATE INDEX sequencingapplication_created_year_idx
    ON public.sequencingapplication USING btree
    ((date_part('year'::text, created)::integer) ASC NULLS LAST)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_createdby_idx
    ON public.sequencingapplication USING gin
    (createdby COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_description_idx
    ON public.sequencingapplication USING gin
    (description COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_description_ts_idx
    ON public.sequencingapplication USING gin
    (to_tsvector('english'::regconfig, COALESCE(description)::text))
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_entity_id_idx
    ON public.sequencingapplication USING btree
    (entity_id ASC NULLS LAST)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_modifiedby_idx
    ON public.sequencingapplication USING gin
    (modifiedby COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_name_idx
    ON public.sequencingapplication USING gin
    (name COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;
CREATE INDEX sequencingapplication_servicetype_id_idx
    ON public.sequencingapplication USING btree
    (servicetype_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE OR REPLACE TRIGGER update_servicetype_deletable_after_sequencingapplicationchange
    AFTER INSERT OR DELETE OR UPDATE OF servicetype_id
    ON public.sequencingapplication
    FOR EACH ROW
    EXECUTE FUNCTION public.update_servicetype_deletable();

Diff delivers:

DROP TRIGGER IF EXISTS update_servicetype_deletable_after_sequencingapplicationchange ON public.sequencingapplication;

Many thanks in advance for looking into it.

Sep 06 '25 02:09 tuerker

@tuerker

The DDL you provided isn’t working on our side, as it depends on other objects or functions that aren’t available in our database. Could you please provide a simplified example that we can use to reproduce the issue?

A couple of clarifications to help us understand better:

Are you comparing two different databases within the same PostgreSQL instance, or are they on separate machines?
Have you tried running the comparison while ignoring the owner and whitespace differences?

Oct 27 '25 10:10 akshay-joshi

No response from the author, hence closing it.

Nov 19 '25 07:11 akshay-joshi