Tom Lane <tgl@xxxxxxxxxxxxx> writes: > Greg Stark <gsstark@xxxxxxx> writes: > > Tom Lane <tgl@xxxxxxxxxxxxx> writes: > >> Direct your complaints to the ISO SQL standards committee. > > > The SQL standard generally treats NULLs as a escape hatch for constraints. Huh? I thought I was agreeing with you. By "escape hatch" I meant that having a NULL value in the indexed column allowed multiple records with otherwise identical values because the NULL compared unequal. Ie, that the unique index doesn't apply to the record with NULL columns. > Not for UNIQUE constraints. SQL92 section 4.10 "Integrity constraints": > > A unique constraint is satisfied if and only if no two rows in > a table have the same non-null values in the unique columns. That's ambiguous. Does it mean no two rows have all non-null columns that are all identical? Or does it mean no two rows have columns that excluding any null columns are identical. It actually sounds more like the latter to me which would mean Postgres's interpretation is wrong. > The short answer to this thread is that the OP is misusing nulls, > and should pick some non-null value to be his "placeholder". We're in "violent agreement". -- greg ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
