Create index with expression on PostgreSQL to find non-zero values

Question

I'm trying to create an optimized, partial index to find all rows where the numeric field amount is not zero.

This

CREATE INDEX amount_not_zero_idx ON my_table (amount)
WHERE amount <> 0;

is working, but it's not really what I have in mind as only the information "unlike zero" is important. What I would like to do is something like

CREATE INDEX amount_not_zero_idx ON my_table (amount <> 0)
WHERE amount <> 0;

as this should give the best performance - but PostgreSQL gives a syntax for the "<>" error.

Any suggestions?

Oxolotl · Accepted Answer · 2017-06-20 07:13:01Z

2

Found it myself. Problem was simply missing brackets, correct syntax is

CREATE INDEX amount_not_zero_idx ON my_table ((amount <> 0))
WHERE amount <> 0;

answered Jun 20, 2017 at 7:13

Oxolotl

4434 silver badges13 bronze badges

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

1 Comment

I typically include the PK columns in a partial index so that it can be used for lookups.