Thread: make \d table Collation field showing domains collation if that attribute is type of domain.
make \d table Collation field showing domains collation if that attribute is type of domain.
From
jian he
Date:
$subject related demos: CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false); create domain d_colli as text collate case_insensitive; create domain d_collc as d_colli collate "C"; create table s1(a d_colli); create table s2(a d_colli[] collate "C"); create table s3(a d_collc); currently in HEAD: \d s1 Table "public.s1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | d_colli | | | \d s2 Table "public.s2" Column | Type | Collation | Nullable | Default --------+-----------+-----------+----------+--------- a | d_colli[] | C | | \d s3 Table "public.s3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | d_collc | | | we can change it to \d s1 Table "public.s1" Column | Type | Collation | Nullable | Default --------+---------+------------------+----------+--------- a | d_colli | case_insensitive | | \d s2 Table "public.s2" Column | Type | Collation | Nullable | Default --------+-----------+-----------+----------+--------- a | d_colli[] | C | | \d s3 Table "public.s3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | d_collc | C | | as you can see: For non-domain types, everything is the same as before. but domains with COLLATION clauses behave differently. main changes: @@ -1866,7 +1866,7 @@ describeOneTableDetails(const char *schemaname, attrdef_col = cols++; attnotnull_col = cols++; appendPQExpBufferStr(&buf, ",\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n" - " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation"); + " WHERE c.oid = a.attcollation AND t.oid = a.atttypid and c.collname <> 'default' ) AS attcollation"); it will make a table column with type "name", display Collation "C", since "name" type typcollation is 'C', other than this all behavior is the same as HEAD. what do you think?
Re: make \d table Collation field showing domains collation if that attribute is type of domain.
From
Tom Lane
Date:
jian he <jian.universality@gmail.com> writes: > main changes: > @@ -1866,7 +1866,7 @@ describeOneTableDetails(const char *schemaname, > attrdef_col = cols++; > attnotnull_col = cols++; > appendPQExpBufferStr(&buf, ",\n (SELECT c.collname > FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n" > - " WHERE > c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> > t.typcollation) AS attcollation"); > + " WHERE > c.oid = a.attcollation AND t.oid = a.atttypid and c.collname <> > 'default' ) AS attcollation"); I doubt that this is an improvement. It will create as many weird cases as it removes. (To cite just one, there is nothing preventing somebody from creating a collation named "default".) The existing definition seems fine to me, anyway. What's so wrong with treating the domain's collation as being an implicit property of the domain type? What you want to do hopelessly confuses this display between attributes of the table and attributes of the column type. A nearby comparable case is that the "Default" column only tells about default values applied in the table definition, not any that might have come from the domain. Nor does the "Check constraints" footer tell about constraints coming from a domain column. regards, tom lane