Navicat导出Postgresql表结构到Excel

最新推荐文章于 2024-09-23 14:16:19 发布

爱写程序的白羊

最新推荐文章于 2024-09-23 14:16:19 发布

阅读量941

点赞数 1

文章标签： postgresql

本文链接：https://blog.csdn.net/qq_41169066/article/details/120010508

版权

该博客内容涉及使用SQL语句从information_schema.columns表中获取指定表（如f_address_by_date）的字段信息，包括序号、字段名、是否为空、数据类型、长度、小数位及是否为主键等关键信息，帮助理解数据库表结构。

摘要由CSDN通过智能技术生成

SELECT
   A.ordinal_position as 序号,
   A.column_name as 字段名,
   CASE A.is_nullable WHEN 'NO' THEN 0 ELSE 1 END AS 是否为空,
   A.data_type as 数据类型,
   coalesce(A.character_maximum_length, A.numeric_precision, -1) as 长度,
   A.numeric_scale as 小数位,
        CASE WHEN length(B.attname) > 0 THEN 1 ELSE 0 END AS 主键
FROM
   information_schema.columns A
LEFT JOIN (
    SELECT
        pg_attribute.attname
    FROM
        pg_index,
        pg_class,
        pg_attribute
    WHERE
        pg_class.oid = 'f_address_by_date' :: regclass  --  替换 f_address_by_date 需要导出的表名
    AND pg_index.indrelid = pg_class.oid
    AND pg_attribute.attrelid = pg_class.oid
    AND pg_attribute.attnum = ANY (pg_index.indkey)
) B ON A.column_name = b.attname
WHERE
   A.table_schema = 'public'
AND A.table_name = 'f_address_by_date' -- 替换 f_address_by_date 需要导出的表名
ORDER BY
   ordinal_position ASC;