PostgreSQL 14 and Unique Index Issue

WarzQ 0
Viewed 25

I have a database I am importing and it has a unique index for a field that is null. In PostgreSQL 13 it was not an issue but in 14 it now longer allows the import as null is no longer null but a value.

Is there a setting where null is treated like it should be instead of as a value?

postgresql
2 Answers

The behavior has not changed in PostgreSQL v14. If the import doesn't work in the database, the only possible explanation is that you have defined the column NOT NULL in one database, but not in the other one (or used a similar check constraint).

PostgreSQL v15 introduces this standard conforming additional clause for unique constraints:

UNIQUE NULLS [NOT] DISTINCT

If you define a unique constraint with NULLS NOT DISTINCT in v15, it will behave differently from prior versions. However, the default is still UNIQUE NULLS DISTINCT.

Laurenz Albe0

when I query the table, the null values are being set as '' during the import, so it fails after the first row. Not sure what changed (other than upgrading to 14.5). Going to reach out to the importer company, I can insert multiple null values so somethings up on their end.

WarzQ0
Related