search

fordfrog / apgdiff

Another PostgreSQL Diff Tool
http://www.apgdiff.com
MIT License
353 stars 138 forks source link

Collation changes can result in bad SQL commands #293

Open jsonn opened 1 year ago

jsonn commented 1 year ago

Before:

CREATE COLLATION public."de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');
CREATE TABLE testtable (
    name text PRIMARY KEY
);

After:

CREATE COLLATION public."de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');

CREATE TABLE testtable (
    name text COLLATE public."de-u-co-phonebk-x-icu" PRIMARY KEY
);

Generated diff from 2.7.0:

ALTER TABLE testtable
        ALTER COLUMN name TYPE text COLLATE public."de-u-co-phonebk-x-icu" PRIMARY KEY USING name::text COLLATE public."de-u-co-phonebk-x-icu" PRIMARY KEY /* TYPE change - table: testtable original: text primary key new: text COLLATE public."de-u-co-phonebk-x-icu" PRIMARY KEY */;

Variations with NOT NULL instead of PRIMARY KEY also trigger invalid SQL.