sql: pg_attribute table lacks the notion of a 'default' collation #54989
Description
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() | NULLExpected 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.