PostgreSQL表结构【仅需要写数据库文档时使用】

最新推荐文章于 2024-02-27 17:15:29 发布

BAT_number

最新推荐文章于 2024-02-27 17:15:29 发布

阅读量514

点赞数

本文链接：https://blog.csdn.net/BAT_number/article/details/88687079

版权

本文主要介绍了如何在PostgreSQL中创建带默认值的表结构，并强调了在编写数据库文档时的注意事项。同时，文章提及了与MySQL的对比。

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

postgreSQL：

select
    a.attname as column_name,
    format_type(a.atttypid,a.atttypmod) as data_type,
    (case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,
    (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,
    (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,
    (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,
    (case when a.attnotnull=true then 'Y' else 'N' end) as nullable,
col_description(a.attrelid,a.attnum) as comment
from pg_attribute a
where attstattarget=-1 and attreli