CODEWITHSUNDEEP
sqlpostgresql
Lachy
Lachy

Reputation: 227

What is the meaning of parentheses around a SQL expression?

I was just reading the docs for partial indexes in Postgres here. The very last code snippet:

CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;

contains some syntax I'm unfamiliar with. In particular it's the (target IS NULL) part. What are the inner parentheses doing here? Usually the syntax of a partial index create command is like:

CREATE UNIQUE INDEX <index_name> ON <table_name>(<column_name>) WHERE <boolean_expression>;

Note: I think that's what the usual syntax is, but I am trying to learn from those docs and they are explaining-by-example, so I may be missing some subtlety here.

Anyway, if this is the case then it seems like (target IS NULL) somehow defines a column. Does the ( . ) operator define an anonymous function which is interpreted as a column here?

I'm quite new to SQL, and this is proving hard to google. Many thanks.

Upvotes: 1

Views: 164

Answers (1)

Joe DiNottra
Joe DiNottra

Reputation: 1008

When creating indexes in PostgreSQL each section can be either a column, or an expression. They are, however, typed differently:

  • To include a column just type its name, as in col1.

  • To include an expression type it in between parenthesis, as in (-col3 * 123).

For example, the following indexes are valid in PostgreSQL:

  • (col1, col2, col3) -- three plain columns.

  • (col1, (-col2), col3) -- two columns and one expression.

  • (col1, (col2 + col3)) -- one column and one expression.

The case you mention target IS NULL is an expression, so it must be typed in parenthesis, as in:

create index idx1 on my_table ((target IS NULL));

As for the "partial index" syntax, that is correct. You add the extra section WHERE <predicate> at the end. That tells PostgreSQL that only rows that match this predicate are included in the index. The rest won't populate the index.

Upvotes: 5

Related Questions