CODEWITHSUNDEEP
databasepostgresqldatabase-indexes
stevec
stevec

Reputation: 52468

Can a database table be indexed on a function of a column?

Can a database index be on a function of a column, or must it be on precisely what's in the column itself without any change/adjustment/calculation?

Simple example:

If a transactions table contains a column that specifies the datetime of the transaction (e.g. 2020-12-13 12:58:59), if we want to index on just the date (e.g. 2020-12-13) of the transaction, does that require another column (with just the date) to be created, or can the index be created on a function of the datetime column?

Upvotes: 0

Views: 86

Answers (2)

sticky bit
sticky bit

Reputation: 37472

Yes it can. It can even be any expression, including function calls, and use more than one column of the table.

From the documentation of CREATE INDEX:

CREATE ... INDEX ... ON ... table_name ...
    ( { ... ( expression ) } ... ) ...

...

expression

An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

Postgres in fact supports function indices. For you example, we can define:

CREATE INDEX idx ON transactions (cast(ts_col AS date));

Upvotes: 4

Related Questions