2

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?

Improve this question
3
  • Yes, there's a setting. The PostgreSQL guys added it in v14 and it now prevent nulls collisions. It's possible to disable it. Let me check... Commented Sep 13, 2022 at 14:33
  • 1
    I was wrong. The new feature (that I personally don't like) will be present in PostgreSQL 15 and adds the clause NULLS [NOT] DISTINCT to the CREATE INDEX statement. See postgresql.org/docs/15/sql-createindex.html If you are using PostgreSQL 14 then the problem is elsewhere. Commented Sep 13, 2022 at 14:39
  • Yes, I see that about 15. Post upgrading to 14.5 it now prevents my import. Commented Sep 13, 2022 at 15:02

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

In the schema that field is set to nullable.... in 13 it worked (my import from Oracle) in 14.5 it no longer works. There must be a setting where null is not treated as a value. sender_id character varying(10) NULL
No, there is no such setting. Perhaps it is not a NULL value after all. Please edit the question and add a sequence of SQL statements that reproduce the problem.
There are no constraints setup for this table yet.
The question is valid and I will work up a test example of the breaking change when I upgraded to 14.5
Claiming that the question is valid doesn't make a difference. I am not saying that is is invalid, I am saying that it is incomplete. It is up to you to prove that what you say really happens by providing a reproducible test case. Otherwise, how shall we reproduce the problem and answer your question?
|
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.

Improve this answer

1 Comment

Confirmed the import tool I was using has two options when importing data: via inserts or via copy. The via inserts replaces nulls with '', the via copy does not change the values of null. Not sure why I didn't notice this. So it has nothing to do with PostgreSQL but that tool.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.