[SA 9.1] bug in Schema Compare working with PostgreSQL

Schema Compare displays Check Contraints always as differing between two PostgreSQL servers (both running 9.5.5) though there are definitely no differences in those Constraints when I compare the output of pg_dump.

I suspect that SQL Assistant might not be prepared that PostgreSQL allows the same name for a Constraint to appear in different tables with different Check code in their body. Schema Compare mixes up the different code inside the Constraints having the same name, and therefore always displays differences.

In the following examples, the same Check Constraint names appear in both tables: chk_abgr_range, chk_idps_range, and chk_iduz_range. PostgreSQL is perfectly fine with that.

Table1:

Code:

CREATE TABLE datbasis (
idmonat SMALLINT NOT NULL,
idtz1 SMALLINT NOT NULL,
idtz2 SMALLINT NOT NULL,
iduz INTEGER NOT NULL,
idps SMALLINT NOT NULL,
idvweg SMALLINT NOT NULL,
abgr SMALLINT NOT NULL,
mdiff SMALLINT NOT NULL,
idtz1v SMALLINT NOT NULL,
idtz2v SMALLINT NOT NULL,
idpsv SMALLINT NOT NULL,
iduzv INTEGER NOT NULL,
errnr SMALLINT NOT NULL,
status SMALLINT NOT NULL,
regel SMALLINT NOT NULL,
tarif SMALLINT NOT NULL,
CONSTRAINT chk_abgr_range CHECK(abgr >= 0 AND abgr <= 99),
CONSTRAINT chk_errnr_range CHECK(errnr >= '-99'::INTEGER AND errnr <= 99),
CONSTRAINT chk_idmonat_range CHECK(idmonat >= 0 AND idmonat <= 999),
CONSTRAINT chk_idps_range CHECK(idps >= '-9999'::INTEGER AND idps <= 9999),
CONSTRAINT chk_idpsv_range CHECK(idpsv >= 0 AND idpsv <= 9999),
CONSTRAINT chk_idtz1_range CHECK(idtz1 >= '-9999'::INTEGER AND idtz1 <= 9999),
CONSTRAINT chk_idtz1v_range CHECK(idtz1v >= '-9999'::INTEGER AND idtz1v <= 9999),
CONSTRAINT chk_idtz2_range CHECK(idtz2 >= '-9999'::INTEGER AND idtz2 <= 9999),
CONSTRAINT chk_idtz2v_range CHECK(idtz2v >= '-9999'::INTEGER AND idtz2v <= 9999),
CONSTRAINT chk_iduz_range CHECK(iduz >= '-999999999'::INTEGER AND iduz <= 999999999),
CONSTRAINT chk_iduzv_range CHECK(iduzv >= 0 AND iduzv <= 999999999),
CONSTRAINT chk_idvweg_range CHECK(idvweg >= 0 AND idvweg <= 99),
CONSTRAINT chk_mdiff_range CHECK(mdiff >= 0 AND mdiff <= 14),
CONSTRAINT chk_regel_range CHECK(regel >= '-999'::INTEGER AND regel <= 999),
CONSTRAINT chk_status_range CHECK(status >= '-999'::INTEGER AND status <= 999),
CONSTRAINT chk_tarif_range CHECK(tarif >= '-9'::INTEGER AND tarif <= 9)
) ;

Table2:

Code:

CREATE TABLE datroh (
idmonat SMALLINT NOT NULL,
idtz1 SMALLINT NOT NULL,
idtz2 SMALLINT NOT NULL,
iduz INTEGER NOT NULL,
idprod SMALLINT NOT NULL,
idps SMALLINT NOT NULL,
verkdiff SMALLINT NOT NULL,
geltdiff SMALLINT NOT NULL,
idvweg SMALLINT NOT NULL,
status SMALLINT NOT NULL,
abgr SMALLINT DEFAULT 0 NOT NULL,
tarif SMALLINT DEFAULT 0 NOT NULL,
CONSTRAINT chk_abgr_range CHECK(abgr >= 0 AND abgr <= 12),
CONSTRAINT chk_geltdiff_range CHECK(geltdiff >= '-99'::INTEGER AND geltdiff <= 99),
CONSTRAINT chk_idmonat_range CHECK(idmonat >= 0 AND idmonat <= 999),
CONSTRAINT chk_idprod_range CHECK(idprod >= 0 AND idprod <= 9999),
CONSTRAINT chk_idps_range CHECK(idps >= 0 AND idps <= 9999),
CONSTRAINT chk_idtz1_range CHECK(idtz1 >= '-9999'::INTEGER AND idtz1 <= 9999),
CONSTRAINT chk_idtz2_range CHECK(idtz2 >= '-9999'::INTEGER AND idtz2 <= 9999),
CONSTRAINT chk_iduz_range CHECK(iduz >= 0 AND iduz <= 999999999),
CONSTRAINT chk_idvweg_range CHECK(idvweg >= '-99'::INTEGER AND idvweg <= 99),
CONSTRAINT chk_status_range CHECK(status >= 0 AND status <= 9999),
CONSTRAINT chk_tarif_range CHECK(tarif >= '-9'::INTEGER AND tarif <= 9),
CONSTRAINT chk_verkdiff_range CHECK(verkdiff >= '-99'::INTEGER AND verkdiff <= 99)
) ;

Please fix Schema Compare to handle PostgreSQL constraints correctly.

Thank you very much for reporting this. I have logged this issue in our bug tracking system.