1

My question is about PostgreSQL. I found similar questions for MS SQL server but I don't know if the answers apply here.

My table looks like this:

scores
======
| ID | UserID | ValidFrom  | ValidUntil | ScorePoints |
+----+--------+------------+------------+-------------|
|  1 |      1 | 2025-08-01 | 2025-08-02 |          80 |
|  2 |      1 | 2025-08-02 |       NULL |          85 |

There will be a lot of queries to find the currently valid score for a given UserID (= the row for UserID = ? AND ValidUntil IS NULL).

I have a unique index like this:

CREATE UNIQUE INDEX ix_uq_scores ON scores ( userid ) WHERE validuntil IS NULL;

Or should it be:

CREATE UNIQUE INDEX ix_uq_scores ON scores ( userid, validuntil ) WHERE validuntil IS NULL;

A query might look like

SELECT u.id, u.username, s.scorepoints 
FROM users u 
INNER JOIN scores s ON s.userid = u.id AND s.validuntil IS NULL
WHERE u.id = 123;

My filtered index will only include rows where validuntil IS NULL. So do I have to add this constant NULL value to the index tuple?

1
  • The second index makes no sense, there is nothing to index on a column that is by law (see the WHERE condition) always NULL. Offtopic: I have a strong preference for the tsrange or tstzrange types, instead of two single columns. See postgresql.org/docs/current/rangetypes.html Commented Aug 7 at 21:07

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.