apgdiff icon indicating copy to clipboard operation
apgdiff copied to clipboard

Collation changes can result in bad SQL commands

Open jsonn opened this issue 1 year ago • 0 comments

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.

jsonn avatar Mar 30 '23 23:03 jsonn