Postgresql数据库级、表级、列级的排序规则
Postgresql数据库级、表级、列级的排序规则
提问于 2019-07-08 19:53:57
我创建了一个带有UTF8字符集的排序规则类型'C‘的数据库。如果我在同一数据库下创建任何表或索引，它是否具有排序规则'C‘，或者我需要在创建表或索引时显式定义。
如何在postgresql 11中提取表和索引的collate详细信息
database create with collate 'C‘也创建了没有显式collate类型的表和数据。
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);需要有关表和索引级别的排序的信息。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-07-08 20:49:15
列的排序规则存储在列的pg_attribute行的attcollation中。值100表示“默认排序规则”，这是存储在pg_database中的数据库排序规则。
这是一种查找表中所有列的排序规则的方法：
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)对于索引，归类存储在列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)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56934368
复制相关文章
相似问题
腾讯云开发者
