sqlpostgresqlcollationpostgresql-10

Postgres COLLATION FOR

I have tried the function collation for(argument) which is described in the PostgreSQL documentation. But when I try to execute the query which is given in the same PostgreSQL documentation, I get an error. Actually, the statement that I ran was

SELECT collation for ('sample') FROM pg_description LIMIT 1;
  1. It could be better if someone gives the exact correct query and the correct parameters for the function.
  2. What is the use case of this function?
  3. If the function name was wrong, please give me the correct function name.
Solution

  • Strictly speaking, COLLATION FOR is not a "function", but a syntax element of SQL. The underlying function in Postgres is pg_collation_for().

    All required information is in the manual, even for your outdated version Postgres 9.5. Search for "collation for" on that manual page and you'll find an example with explanation.

    But it's more clearly documented in the current version (quote from pg 15):

    Returns the name of the collation of the value that is passed to it. The value is quoted and schema-qualified if necessary. If no collation was derived for the argument expression, then NULL is returned. If the argument is not of a collatable data type, then an error is raised.

    Bold emphasis mine.

    Basically, only string types like text and varchar are collatable.

    No error should happen for the example query from the manual as pg_description.description is type text. But you ran a different one:

    SELECT collation for ('sample') FROM pg_description LIMIT 1;

    Which can be shortened to just:

    SELECT collation for ('sample');

    'sample' is an untyped string literal, i.e. type "unknonwn", but it will be coerced to text by default, so it should not error out, either.