PostgreSQL 表达式索引 - 语法注意事项
背景
表达式索引是非常有用的功能之一，但是使用时语法上要注意一下，表达式需要用括号括起来
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.
仅仅当表达式是单一的函数时，不需要括号。
例子
如果表达式不是函数，则会有这样的错误
postgres=# create table t2(c1 text, c2 text);
CREATE TABLE
postgres=# create index idx1_t2 on t2 (c1||c2);
ERROR: 42601: syntax error at or near "||"
LINE 1: create index idx1_t2 on t2 (c1||c2);
^
LOCATION: scanner_yyerror, scan.l:1081
报错的原因是语法不支持。
解决办法，表达式用括号括起来
postgres=# create index idx1_t2 on t2( (c1||c2) );
CREATE INDEX
类似的例子还很多
postgres=# create index idx1_t4 on t2 (c1::int);
ERROR: syntax error at or near "::"
LINE 1: create index idx1_t4 on t2 (c1::int);
^
postgres=# create index idx1_t3 on t2 ((c1::int));
CREATE INDEX
postgres=# create index idx1_t5 on t2 ( substring(c1,1,2) || 'abc' );
ERROR: syntax error at or near "||"
LINE 1: create index idx1_t5 on t2 ( substring(c1,1,2) || 'abc' );
^
postgres=# create index idx1_t5 on t2 ( (substring(c1,1,2) || 'abc') );
CREATE INDEX