Thread: BUG #8211: Syntax error when creating index on expression

The following bug has been logged on the website:

Bug reference:      8211
Logged by:          Andrey Cizov
Email address:      acizov@gmail.com
PostgreSQL version: 9.2.3
Operating system:   Windows
Description:        =


CREATE INDEX heuristic ON foos (1 / (a + b))

causes: =


ERROR:  syntax error at or near "1"
LINE 1: CREATE INDEX heuristic ON foos (1 / (a + b))
                                        ^

********** Error **********

ERROR: syntax error at or near "1"
SQL state: 42601
Character: 33

While:

CREATE INDEX heuristic ON foos ((1 / (a + b)))

Execution successful

On Wed, Jun 5, 2013 at 8:24 AM, <acizov@gmail.com> wrote:
>
> Bug reference:      8211
> Logged by:          Andrey Cizov
> Email address:      acizov@gmail.com
> PostgreSQL version: 9.2.3
> Operating system:   Windows
> Description:
>
> CREATE INDEX heuristic ON foos (1 / (a + b))
>
> causes:
>
> ERROR:  syntax error at or near "1"
> LINE 1: CREATE INDEX heuristic ON foos (1 / (a + b))
>                                         ^
>
> ********** Error **********
>
> ERROR: syntax error at or near "1"
> SQL state: 42601
> Character: 33
>
> While:
>
> CREATE INDEX heuristic ON foos ((1 / (a + b)))
>
> Execution successful



Did you look at the docs?

http://www.postgresql.org/docs/current/static/sql-createindex.html

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.

acizov@gmail.com writes:
> CREATE INDEX heuristic ON foos (1 / (a + b))
> causes:
> ERROR:  syntax error at or near "1"

This is not a bug.  You need an extra pair of parentheses around
the expression, ie

CREATE INDEX heuristic ON foos ((1 / (a + b)))

http://www.postgresql.org/docs/9.2/static/sql-createindex.html
points this out both in the syntax diagram and the text.

            regards, tom lane

bricklen wrote
> 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.

So in fact the example provided:

CREATE INDEX ON films ((lower(title)));

could be written as:

CREATE INDEX ON films (lower(title));

The example expression has yet one additional pair of "()" that are not
required per the syntax since lower(...) is a function call.  Extra "()"
never hurt I suppose...

I don't see this come up too often on the list but I will agree that it is
unexpected to require the extra set of "()".  An example using an actual
expression with the extra "()" and then the function call example without -
to explicitly show when/why they can be omitted in the examples as well as
in the text - is an idea worth considering.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-8211-Syntax-error-when-creating-index-on-expression-tp5758030p5758040.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.