1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] How to list constraints of a table in PostgreSQL without using information_schema?

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 14, 2021.

  1. Stack

    Stack Membro Participativo

    Is it possible to get the same result as the following SQL statement without using information_schema, using only pg_catalog, etc.?

    This SQL statement is a table that joins primary key constraints with foreign key constraints, and additionally, each information has a position, column type, and whether it is nullable.

    SELECT
    inf_c.table_name AS table_name,
    inf_c.ordinal_position AS ordinal_position,
    inf_c.column_name AS column_name,
    inf_c.data_type AS data_type,
    inf_c.character_maximum_length AS character_maximum_length,
    inf_c.character_octet_length AS character_octet_length,
    inf_c.numeric_precision AS numeric_precision,
    inf_c.numeric_scale AS numeric_scale,
    inf_c.character_set_name AS character_set_name,
    inf_c.column_default AS column_default,
    inf_c.is_nullable AS is_nullable,
    CASE
    WHEN primary_keys.column_name IS NOT NULL THEN 'YES'
    ELSE 'NO'
    END AS is_primary_key,
    pg_d.description AS column_comment,
    foreign_keys.foreign_table_name AS foreign_table,
    foreign_keys.foreign_column_name AS foreign_column,
    inf_c.collation_name AS collation_name
    FROM
    information_schema.columns AS inf_c
    LEFT JOIN
    pg_stat_user_tables AS pg_s
    ON pg_s.relname = inf_c.table_name
    AND pg_s.schemaname = inf_c.table_schema
    LEFT JOIN
    pg_description AS pg_d
    ON pg_s.relid = pg_d.objoid
    AND inf_c.ordinal_position = pg_d.objsubid
    LEFT JOIN
    (
    SELECT
    tb_con.table_name AS table_name,
    ccu.column_name AS column_name
    FROM
    information_schema.table_constraints tb_con
    INNER JOIN
    information_schema.constraint_column_usage ccu
    ON tb_con.constraint_catalog = ccu.constraint_catalog
    AND tb_con.constraint_schema = ccu.constraint_schema
    AND tb_con.constraint_name = ccu.constraint_name
    INNER JOIN
    information_schema.key_column_usage kcu
    ON tb_con.constraint_catalog = kcu.constraint_catalog
    AND tb_con.constraint_schema = kcu.constraint_schema
    AND tb_con.constraint_name = kcu.constraint_name
    AND ccu.column_name = kcu.column_name
    WHERE
    tb_con.constraint_type = 'PRIMARY KEY'
    AND tb_con.table_schema = '<schema_name>'
    ) AS primary_keys
    ON primary_keys.table_name = inf_c.table_name
    AND primary_keys.column_name = inf_c.column_name
    LEFT JOIN
    (
    SELECT
    tb_con.table_name AS table_name,
    kcu.column_name AS column_name,
    ref_tb_con.table_name AS foreign_table_name,
    ref_ccu.column_name AS foreign_column_name,
    tb_con.constraint_name AS constraint_name
    FROM
    information_schema.table_constraints AS tb_con
    INNER JOIN
    information_schema.key_column_usage AS kcu
    ON tb_con.constraint_catalog = kcu.constraint_catalog
    AND tb_con.constraint_schema = kcu.constraint_schema
    AND tb_con.constraint_name = kcu.constraint_name
    INNER JOIN
    information_schema.referential_constraints AS ref_con
    ON tb_con.constraint_catalog = ref_con.constraint_catalog
    AND tb_con.constraint_schema = ref_con.constraint_schema
    AND tb_con.constraint_name = ref_con.constraint_name
    INNER JOIN
    information_schema.table_constraints AS ref_tb_con
    ON ref_con.unique_constraint_catalog = ref_tb_con.constraint_catalog
    AND ref_con.unique_constraint_schema = ref_tb_con.constraint_schema
    AND ref_con.unique_constraint_name = ref_tb_con.constraint_name
    INNER JOIN
    information_schema.constraint_column_usage AS ref_ccu
    ON ref_tb_con.constraint_catalog = ref_ccu.constraint_catalog
    AND ref_tb_con.constraint_schema = ref_ccu.constraint_schema
    AND ref_tb_con.constraint_name = ref_ccu.constraint_name
    INNER JOIN
    information_schema.key_column_usage AS ref_kcu
    ON ref_tb_con.constraint_catalog = ref_kcu.constraint_catalog
    AND ref_tb_con.constraint_schema = ref_kcu.constraint_schema
    AND ref_tb_con.constraint_name = ref_kcu.constraint_name
    AND ref_ccu.column_name = ref_kcu.column_name
    AND kcu.ordinal_position = ref_kcu.ordinal_position
    WHERE
    tb_con.constraint_type = 'FOREIGN KEY'
    AND tb_con.table_schema = '<schema_name>'
    ORDER BY
    tb_con.table_catalog,
    tb_con.table_name,
    tb_con.constraint_name,
    kcu.ordinal_position
    ) AS foreign_keys
    ON foreign_keys.table_name = inf_c.table_name
    AND foreign_keys.column_name = inf_c.column_name
    WHERE
    inf_c.table_schema = '<schema_name>'
    ORDER BY
    inf_c.table_name,
    inf_c.ordinal_position
    ;

    Continue reading...

Compartilhe esta Página