To create index with certain length I use the following query:
CREATE INDEX users_full_name_idx ON users ((full_name::varchar(16)));
This code works fine. However, if I try to do the following:
CREATE INDEX users_full_name_idx ON users (full_name::varchar(16));
I get error. Could anyone explain why we need two braces but not one.
It is index on expression:
An index column need not be just a column of the underlying table, but can be a function or scalar expression computed from one or more columns of the table. This feature is useful to obtain fast access to tables based on the results of computations.
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1)); CREATE INDEX people_names ON people ((first_name || ' ' || last_name));The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions, as shown in the second example. The parentheses can be omitted when the expression is just a function call, as in the first example.
and CREATE INDEX:
The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses.
