digoal
2016-08-03
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
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
