sql: syntax error: invalid locale POSIX: language: tag is not well-formed

[giangpham712]

Describe the problem

Trying to create a table using the following SQL:

CREATE TABLE columns_with_collation (
    id int,
    default_collation TEXT,
    non_default_collation TEXT COLLATE "POSIX"
);

returns error

ERROR: at or near ")": syntax error: invalid locale POSIX: language: tag is not well-formed

The following query

SELECT collname from pg_catalog.pg_collation;

shows POSIX as an available collation

oid     |      collname       | collnamespace | collowner | collencoding | collcollate | collctype | collprovider | collversion | collisdeterministic
-------------+---------------------+---------------+-----------+--------------+-------------+-----------+--------------+-------------+----------------------
  3403232968 | default             |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   125208652 | C                   |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
  1091284106 | POSIX               |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   194813764 | und                 |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   764986699 | aa                  |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   764986700 | af                  |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   764986712 | ar                  |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   764986713 | as                  |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   764986704 | az                  |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   748209138 | be                  |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   748209136 | bg                  |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
   748209145 | bn                  |    4294967114 |      NULL |            6 | NULL        | NULL      | NULL         | NULL        |        NULL
...

Additional context
This affects efcore.pg tests

@fqazi

Jira issue: CRDB-30569

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[fqazi]

This requires some more complex work. The collation type is just not handled properly at:

cockroach/pkg/sql/sem/tree/create.go

Line 586 in 2468ed7

if locale != collatedstring.DefaultCollationTag {

[fqazi]

@giangpham712 Please skip tests declaring this column type for the moment