PostgreSQL 数据库如何获取表字段的所有关键信息包含主键以及索引键等

要获取 PostgreSQL 数据库中表字段的所有关键信息，包括主键和索引键等，请使用以下查询语句：

SELECT
    c.column_name,
    c.data_type,
    c.character_maximum_length,
    c.is_nullable,
    c.column_default,
    CASE
        WHEN tc.constraint_type = 'PRIMARY KEY' THEN true
        ELSE false
    END AS is_primary_key,
    CASE
        WHEN ix.indexrelid IS NOT NULL THEN true
        ELSE false
    END AS is_indexed
FROM
    information_schema.columns AS c
LEFT JOIN
    information_schema.key_column_usage AS kcu ON c.table_schema = kcu.table_schema AND c.table_name = kcu.table_name AND c.column_name = kcu.column_name
LEFT JOIN
    information_schema.table_constraints AS tc ON kcu.constraint_name = tc.constraint_name
LEFT JOIN
    (
        SELECT
            i.relname AS index_name,
            a.attname AS column_name,
            t.relname AS table_name,
            t.relkind
        FROM
            pg_class t,
            pg_class i,
            pg_index ix,
            pg_attribute a
        WHERE
            t.oid = ix.indrelid
            AND i.oid = ix.indexrelid
            AND a.attrelid = t.oid
            AND a.attnum = ANY(ix.indkey)
            AND t.relkind = 'r'
    ) AS ix ON c.table_schema = ix.table_name AND c.column_name = ix.column_name
WHERE
    c.table_schema = 'your_schema_name'
    AND c.table_name = 'your_table_name'
ORDER BY
    c.ordinal_position;

请将 `your_schema_name` 替换为您的模式名称，将 `your_table_name` 替换为您的表名称。这个查询将返回给定表的字段名称、数据类型、最大字符长度（如果适用）、是否可为空、默认值、是否为主键和是否有索引的信息。结果按照字段在表中的顺序进行排序。