0

Here, a lot of energy seems spent on Coalesce - I think it is to avoid the case where a null value in the update record could overwrite a non-null value.

Say we have a table of businesses and some field, let's say Address 2 is 'Second Floor' or Suite 123 or something not mission-critical, but important. For some reason, the owners or some directory owner removes the Address 2 field from the directory we are scraping or set Address 2 to NULL. In this case, we know something that the UPSERT/UPDATE doesn't know and it's valuable.

Now, on the other hand, maybe there is a field, a dumb example, like AOL Address in our database, and the business finally drops their AOL account, or updates their email to a Gmail one...so the AOL Address field comes in as NULL. In this case, the update record knows something new.

Can anyone answer whether UPSERT will update DATA with NO DATA/NULL? Can it be controlled? Is there yet a ChatGPT Python package that will make such decisions for me? If not, what is taking so long? :)

So, I would call my first example above an undesirable update, and the second one a desirable update, but until the ChatGPT thing is created, Postgres probably doesn't know which is which.

I assume the Postgres team did the smartest thing possible, but just don't know, and haven't found an understandable explanation, of what that is.

Can anyone lead me to the light on this issue?

Improve this question
0

1 Answer 1

Reset to default
2

Can anyone answer whether UPSERT will update DATA with NO DATA/NULL?

INSERT and UPDATE write a null value if given, just like any other value. But I suspect that's not exactly what you wanted to ask. See below.

It's unclear, what "no data" is supposed to be. String types have the empty string '', which has always been just another value. Most other data types have no provision for "no data". (null is typically interpreted to mean "unknown", but that's up to you, really.) If you mean "no row", then nothing happens at all if there is no input row.

It's unclear how ChatGPT or Python come into this.

Postgres probably doesn't know which is which.

Postgres just does what you tell it to do. It doesn't "know" anything. (Neither does ChatGPT, if you ask me, but let's stay on topic.)

What you didn't ask

I think all the confusion really stems from UNIQUE indexes considering multiple null to be distinct. Hence, null values would never raise a unique violation. That's according to the SQL standard.
Postgres 15 added the NULLS NOT DISTINCT clause to toggle that behavior for UNIQUE indexes. See:

Now you can treat null as just another value if you wish.

I also updated my old answer you referenced at the outset:

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.