Daniel Gustafsson <daniel@yesql.se> writes: > On 14 Dec 2022, at 13:54, David G. Johnston <david.g.johnston@gmail.com> wrote: >> There is a decent chance that the fix here is to prohibit doing what you did here - a PK cannot contain nulls in any of its columns so indeed choosing an index that specifies how nulls behave is non-sensical. That said, it also doesn’t hurt so long as the column itself is indeed not null. But extending the syntax doesn’t seem that appealing.
> Even if we prohibit this, there is still the case of all existing systems which > can't be dumped. I wonder if the solution is to teach pg_dump to not create > NULLS NOT DISTINCT primary key constraints? The simple attached fix creates a > valid PK constraint on the above schema.
It doesn't make sense for pg_dump to editorialize on a schema that we otherwise consider valid; people would rightfully complain that dump/restore changed things. So we need to do both things: prohibit adopting such an index as a PK constraint (but I guess it's okay for plain unique constraints?), and adjust pg_dump to compensate for the legacy case where it was already done.
The WHERE clause of CREATE INDEX doesn't pose an issue as we report "Cannot create a primary key or unique constraint using such an index".
It is also not possible to specify an opclass in PRIMARY KEY, but since we document that unique indexes are only currently implemented by B-tree, and that is what you get from PRIMARY KEY, that is also not a problem (presently - unless extension authors were to bypass this).
The remaining elements: INCLUDE, WITH, and TABLESPACE, all exist in both formulations.
I am thinking now that the failure to include NULLS [NOT[ DISTINCT in the CREATE TABLE syntax is an oversight that needs to be fixed. It just doesn't make sense to have the two commands expose different features.
