Re: Index of expression over table row or column

[Steve Lau]

Hi

> On Oct 16, 2024, at 12:17 PM, Laurenz Albe  wrote:
> 
> And what would you say about this (silly) example:
> 
>  CREATE TABLE x (a integer, b integer);
>  CREATE INDEX ON x(hash_record(x));

When I talk about an expression over something, I mainly think about it at the 
AST level, I guess the AST of expression “hash_record(x)” will be something 
like (I tried to parse this statement and print the AST using pg_query.rs, but 
looks like this library does not have an AST type defined, sorry if my guess is 
too incorrect):

FunctionCall {
name: “hash_record",
arguments: [
Table {
name: "x"
}
]
}

So it is not table columns or rows IMHO.

Regards, Steve.

Index of expression over table row or column

[Steve Lau]

Hi, folks!

I am reading this documentation[1], and it has a sentence that I don’t quite 
understand: "The index columns (key values) can be either simple columns of the 
underlying table or expressions over the table rows.”, I am thinking that for 
the index of expressions, aren’t those expressions over table column? e.g., 
“CREATE INDEX idx_lower_last_name ON users(LOWER(last_name))”, “last_name" is a 
column rather than a row.


[1] https://www.postgresql.org/docs/17/index-api.html#INDEX-API

Regards, Steve.

Re: Index of expression over table row or column

[Steve Lau]

Hi, thanks both for the reply.

> The description for pg_index.indkey uses the phrasing “an expression over the 
> table columns” and this should be made to match.

Thanks David for showing me that existing documentation, I agree we should make 
them match.

Regarding Tom’s reply, IMHO, “LOWER(last_name || ' ' || first_name)” is still 
an expression over table columns? Would you like to elaborate on it a bit?

Regards, Steve.