Does PostgreSQL index null values?

Question

If I have a table like so:

Food
-----------------
name | price | x

Let's say I create an index on name & price, but the database has a bunch of null values for both name & price in some of the rows. Does PostgreSQL automatically skip indexing those rows?

Try inserting null values and check to see if it was indexed? — Jacob H
– Jacob H, Commented Jun 21, 2018 at 19:32
Please do not crosspost. Also asked and answered here: dba.stackexchange.com/questions/210292 — user330315
– user330315, Commented Jun 21, 2018 at 20:30

nzifnab · Accepted Answer · 2021-10-22 21:26:52Z

14

NULL values ARE indexed as well.

You can also use an index to speed up queries with a condition like

WHERE col IS NULL

Something that may come as a surprise to Oracle users is that you can have several rows with a NULL in a unique index. But that makes sense because NULL = NULL is not true.

edited Oct 22, 2021 at 21:26

nzifnab

16.2k3 gold badges53 silver badges67 bronze badges

answered Jun 21, 2018 at 19:37

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Klesun Over a year ago

Will such index work for LEFT JOIN queries?

Laurenz Albe Over a year ago

@Klesun If you want to know if you can use an index to identify NULLs which are generated as the result of an outer join, no. These NULLs don't exist in the table, so they cannot be indexed. But an index can speed up a join for other reasons.

Collectives™ on Stack Overflow

Does PostgreSQL index null values?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related