CODEWITHSUNDEEP
postgresqlcollation
Ishan
Ishan

Reputation: 15

Collation in Postgresql DB level,table level, column level

I have database created with Collation type 'C' with UTF8 characterset. If I create any table or index under same database will it be having the Collation 'C' or I need to explicitly define at the time on table or index creation.

How we can extract the details of collate for table and indexes in postgresql 11

database create with collate 'C' also created table and idexes without explicitly collate type.

CREATE DATABASE testdb
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'C'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = testts
    CONNECTION LIMIT = -1
    TEMPLATE = template0;

create table test1c (id integer, content varchar(10));
create index idx_test on test1c(content);

Need information about collate on table and index level.

Upvotes: 0

Views: 6064

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246043

The collation of a column is stored in the attcollation of the column's pg_attribute row. A value of 100 signifies the “default collation”, which is the database collation stored in pg_database.

This is a way to find the collation of all columns for a table:

WITH defcoll AS (
   SELECT datcollate AS coll
   FROM pg_database
   WHERE datname = current_database()
)
SELECT a.attname,
       CASE WHEN c.collname = 'default'
            THEN defcoll.coll
            ELSE c.collname
       END AS collation
FROM pg_attribute AS a
   CROSS JOIN defcoll
   LEFT JOIN pg_collation AS c ON a.attcollation = c.oid
WHERE a.attrelid = 'test1c'::regclass
  AND a.attnum > 0
ORDER BY attnum;

 attname | collation 
---------+-----------
 id      | 
 content | C
(2 rows)

For indexes, the collations are stored in the column pg_index.indcollation:

WITH defcoll AS (
   SELECT datcollate AS coll
   FROM pg_database
   WHERE datname = current_database()
)
SELECT icol.pos,
       CASE WHEN c.collname = 'default'
            THEN defcoll.coll
            ELSE c.collname
       END AS collation
FROM pg_index AS i
   CROSS JOIN unnest(i.indcollation) WITH ORDINALITY AS icol(coll, pos)
   CROSS JOIN defcoll
   LEFT JOIN pg_collation AS c ON c.oid = icol.coll
WHERE i.indexrelid = 'idx_test'::regclass
ORDER BY icol.pos;

 pos | collation 
-----+-----------
   1 | C
(1 row)

Upvotes: 3

Related Questions