sql: pg_attribute table lacks the notion of a 'default' collation

[timgraham]

Describe the problem

Unlike PostgreSQL, CockroachDB reports a column's collation, even if none is given when creating the column.

To Reproduce

CREATE TABLE test (name VARCHAR(255) NOT NULL);
SELECT
    a.attname AS column_name,
    NOT (a.attnotnull OR (t.typtype = 'd' AND t.typnotnull)) AS is_nullable,
    pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
    CASE WHEN collname = 'default' THEN NULL ELSE collname END AS collation
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
LEFT JOIN pg_collation co ON a.attcollation = co.oid
JOIN pg_type t ON a.atttypid = t.oid
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
    AND c.relname = 'test'
    AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
    AND pg_catalog.pg_table_is_visible(c.oid);

  column_name | is_nullable | column_default | collation
--------------+-------------+----------------+------------
  name        |    false    | NULL           | en-US
  rowid       |    false    | unique_rowid() | NULL

Expected behavior

collation for the name column should be NULL rather than en-US.

Environment:

• CockroachDB version v20.2.0-alpha.1-3651-g1be3227203 @ 2020/09/26 05:50:03

Additional context

Django 3.2 adds support for column collations. This causes Django's inspectdb utility to add the db_collation option to all CharFields. If this behavior can't or won't change, a workaround in the django-cockroachdb adapter may be possible by modifying the CASE WHEN collname = 'default' SQL with a retrieval of the default collation.

[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:

• @rafiss (found keywords: Django)
• @solongordon (found keywords: JOIN,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 otan.}

[rafiss]

Potentially related to #54817