Geek asks and answers

Create a multi-column index to ensure uniqueness

I have two fields in my table which, if they (tat and dim) are equal, I discard them i.e.

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

This is considered invalid for identifiers 1and 2. Because I want to allow one dimfor tat. So I have a script that cleared these duplicates for me, and after that I added an index to this table to prevent this from happening:

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

As explained well in this answer:

stack overflow

Now go ahead 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 a combination to be entered 1, NULL, but even more (1,2)so (1,3)?

0
1

.

, , , :
PostgreSQL

(, ), . (1, NULL) , (1,2) (1,3) .

('') ( ) NULL: . , , ( ):

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

(1, 'a'), (1, 'b') .
(1, NULL) (1, '') .

(tat).
, . , :

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

.. :

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

.. . ( '' NULL). dba.SE.

+2

More articles:


All Articles