Create a multicolumn index to enforce uniqueness

Question

I've got two fields in my table that if they(tat and dim) are equal I'm discarding them i.e

id| tat | dim | visible
1 | 11  | 22  | true
2 | 11  | 22  | false

This is considered not to be valid entry for ids 1 and 2 . Because I want to allow one dim per tat. So I have a script that cleaned these duplicates for me and after that I added index on this table to prevent this from happening like this :

CREATE UNIQUE INDEX dim_tat_idx
ON predictions (tat, dim);

As explained quite nicely in this answer:

https://mcmap.net/q/749819/-how-to-add-a-conditional-unique-index-on-postgresql

Now going further in this case

This way you can enter (1,2), (1,3) and (1, NULL)
but neither of them a second time

What type of index would I create or what other mechanism would I use at the database level to allow entering 1, NULL combination but prevent (1,2) and (1,3) more times?

Printer

You can add more than one rows with (1,NULL) with the unique index you have. – Krusche

The question you have linked, provides how to change this (default and standard) behaviour and disallow more than one row with NULL. – Krusche

@ypercube how can I do that can you answer ? – Printer

Check this SQL-Fiddle. Is that ok? I don't see where the problem is really. – Krusche

I would like you to post some answer because the index wasn't the issue here, this issue I was having is that my values which I inserted weren't actually NULL but rather they were empty string so I feel if I delete this question someone else might be stripped of useful info if you agree reply that is .. thanks though – Printer

@GandalfStormCrow, you can answer yourself, I think will be most appropriate in this case. – Egress

Answer 1

This seems to be a misunderstanding.

Your quote from my answer is a bit misleading, since it only applies if you also create the additional partial index as described over there:
How to add a conditional unique index on PostgreSQL

If you do not add this second index (like you did not), you already have your solution, it would seem. With the multicolumn unique index alone, you can enter (1, NULL) multiple times, but (1,2) or (1,3) only once.

Empty strings

If, by mistake, you were considering empty strings ('') (for a character type) instead of NULL values: those are handled like any other value. You could deal with this situation using a multicolumn, partly functional unique index (index on an expression):

CREATE UNIQUE INDEX predictions _dim_tat_uni_idx
ON predictions (tat, NULLIF(dim, ''));

This way you can enter (1, 'a'), (1, 'b') only once.
But (1, NULL) and (1, '') multiple times.

Side effects

The index would still fully support plain queries on the first column (tat).
But queries on both columns would have to match the expression to utilize the full potential. This would be faster, even if it doesn't seem to make sense:

SELECT * FROM predictions
WHERE  tat = 1
AND    NULLIF(dim, '') = 'foo';

.. than this:

SELECT * FROM predictions
WHERE  tat = 1
AND    dim = 'foo';

.. because the first query can use both index columns. Result would be the same (except when searching for '' or NULL). Details in this related answer on dba.SE.

Av