Question 114653 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL 14 and Unique Index Issue

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search