Collation in Postgresql DB level,table level, column level

Question

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.

Laurenz Albe · Accepted Answer · 2019-07-08 12:49:14Z

3

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)

answered Jul 8, 2019 at 12:49

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ishan Over a year ago

So Does it means, That If I create my database with specific Collate Type, the the objects (Tables,Indexes, columns) will be having the same Collate type right?

Laurenz Albe Over a year ago

Yes, unless you explicitly specify a different collation.

Collectives™ on Stack Overflow

Collation in Postgresql DB level,table level, column level

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related