CODEWITHSUNDEEP
sqldatabasepostgresqlcollation
David542
David542

Reputation: 110093

What is the precedence of the `COLLATE` operator?

What is the precedence of the COLLATE operator? For example, in the following:

SELECT 'a' || 'B' COLLATE someting

I believe in this particular case it is interpreted as:

SELECT 'a' || ('B' COLLATE something)

But is there a listing of the exact rules on where the COLLATE keyword falls in? Is that section that says "All other operators" ?

Upvotes: 2

Views: 191

Answers (2)

Rene Saarsoo
Rene Saarsoo

Reputation: 13907

I have found the PostgreSQL documantation for operator precedence to be frustratingly lacking. While the SQL syntax documentation does state that:

The COLLATE clause binds tighter than operators

This is not correct.

PostgreSQL documentation lists ., :: and [] as operators in the operator-precedence table, and these bind tighter than COLLATE, as demonstrated by the following expressions not causing an error:

SELECT foo.bar COLLATE "POSIX" FROM foo;
SELECT 'Hello' :: VARCHAR COLLATE "POSIX";
SELECT t.arr[1] COLLATE "C" FROM (SELECT ARRAY['Hello'] AS arr) t;

At the same time the operators in the "any other operators" category bind looser than COLLATE. For example the || operator in question:

SELECT 2 || 'Hello' COLLATE "C"; -- works fine
SELECT 'Hello' || 2 COLLATE "C"; -- throws error

It also binds tighter than the AT TIME ZONE operator (another operator that's not listed in the operator precedence table). As demonstrated by the following code:

SELECT TIME '12:30' AT TIME ZONE 'UTC' COLLATE "C"; -- works fine
SELECT TIME '12:30' COLLATE "C"; -- throws error

I have determined through experimentation that AT TIME ZONE binds tighter than unary +/- and looser than exponentiation (^).

The COLLATE operator also binds tighter than unary +/-:

-- throws error: collations are not supported by type double precision
SELECT +'2' COLLATE "C";

I hereby present a revised PostgreSQL operator precedence table:

  • . (table/column name separator)
  • :: (type cast)
  • () (function call)
  • [] (array accessor)
  • + - (unary plus, minus)
  • COLLATE
  • AT TIME ZONE AT LOCAL
  • ^ (exponentiation)
  • * / % (multiplication, division, modulo)
  • + - (addition, subtraction)
  • any other operator (e.g. ||, >>, |/, #, ~)
  • ESCAPE
  • BETWEEN IN LIKE ILIKE SIMILAR
  • < > = <= >= <> != (comparison)
  • IS ISNULL NOTNULL IS DISTINCT FROM, etc.
  • NOT
  • AND
  • OR
  • INTERSECT
  • UNION EXCEPT

Definitive source: PostgreSQL source code.

Update: The latest development version of the docs now includes the COLLATE and AT operators.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246053

As so often, the PostgreSQL documentation delivers:

The COLLATE clause binds tighter than operators; parentheses can be used when necessary.

Upvotes: 2

Related Questions