0

I am curious if MySQL has a method as well here.

We have a TON of sparse tables (health industry can be a bit of a pain in this regard) with a ton of columns that can be null(or have no value / unknown. Our microservice on top of the DB is java which supports null values(or for us not exist). We also use hibernate which pre-compiles @NamedQuery JQL into SQL ahead of time(so no way to translate u.col = :col at query translation time to include u.col is null vs. u.col = null). This means hibernate generates u.col = null which is not really SQL compliant(which is really too bad as it would be super nice and has been a source of confusion for each developer we hire...even some senior devs. I don't understand why they don't just eliminate the confusion there - at least for langs with null values it would be nice).

We then have as an example(but usually more like 5/6 columns)

firstName, middleName, lastName

and I think it would be clear we don't want

John null Smith

twice in the database. That just makes no sense. Unfortunately, we can have 2/3 columns with nulls. The SQL standard then is allowing us to have tons of duplicates and KNOW which values can be null WAY ahead of time (We just don't know as each customer keeps coming up with unique situations where all of the sudden one of those columns is null).

This means doing something like the FIRST answer in this post below is not very useful for us (as you have to know ALL situations ahead of time)...

Create unique constraint with null columns

(He has a great answer there!!) Alas, we would have to guess and it will be wrong. Is there any way we can tell postgres to create an index on 5 columns and have the nulls be unique rather than not matching.

The other way is defaulting EVERY value in postgres possible to 0 length string. I am not sure how postgres treats those though. I remember oracle just treated that as null.

Any ideas on how to deal with postgres here? Does MySQL have something that can also fix this?

thanks, Dean

Dean Hiller
  • 19,235
  • 25
  • 129
  • 212

1 Answers1

2

You cannot change the semantics on NULL in unique constraints.

But in PostgreSQL you can use a unique index that treats the NULLs like empty strings:

CREATE UNIQUE INDEX ON the_table (
   coalesce(firstname, ''),
   coalesce(middlename, ''),
   coalesce(lastname, '')
);

That should do exactly what you want.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263