sql - 在 PostgreSQL 中创建一定长度的索引时用双括号

Question

要创建具有特定长度的索引，我使用以下查询:

CREATE INDEX users_full_name_idx ON users ((full_name::varchar(16)));

这段代码工作正常。但是，如果我尝试执行以下操作:

CREATE INDEX users_full_name_idx ON users (full_name::varchar(16));

我收到错误。谁能解释为什么我们需要两个而不是一个牙套。

Answer 1

是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.

和CREATE INDEX :

The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses.