11 ms·
A lot of these aren't postgres-specific. (null weirdness, index column order, etc.) For example, how nulls work - especially how interact with indexes and uniq
by pavel_lishin 2y ago
A lot of these aren't postgres-specific. (null weirdness, index column order, etc.)
For example, how nulls work - especially how interact with indexes and unique constraints - is also non-intuitive in mysql.
If you have a user table with a non-nullable email column and a nullable username column, and a uniqueness constraint on something like (email, username), you'll be able to insert multiple identical emails with a null username into that table - because a null isn't equivalent to another null.
- anarazel 2y ago> If you have a user table with a non-nullable email column and a nullable username column, and a uniqueness constraint on something like (email, username), you'll be able to insert multiple identical emails with a null username into that table - because a null isn't equivalent to another null. FWIW, since 15 postgres you can influence that behaviour with NULLS [NOT] DISTINCT for constraints and unique indexes. https://www.postgresql.org/docs/devel/sql-createtable.html#SQL-CREATETABLE-PARMS-UNIQUE https://www.postgresql.org/docs/devel/sql-createtable.html#S... EDIT: Added link
- bpicolo 2y agoI think this is a good pragmatic default. The use case for the alternative is much more rare.
- pavel_lishin 2y agoI totally agree - but it's not an intuitive default.