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

最新推荐文章于 2024-08-13 16:05:03 发布

原创最新推荐文章于 2024-08-13 16:05:03 发布 · 586 阅读

0 ·

CC 4.0 BY-SA版权

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 attrelid = (select oid from pg_class where relname ='table_name'); -- 表名

postgreSQL升级版（带默认值，但是得替换两处表名）

select
	b.ordinal_position as 字段位置,
    a.attname as 列名,
    format_type(a.atttypid,a.atttypmod) as 类型,
    (case when atttypmod-4>0 then atttypmod-4 else 0 end) 长度,
	b.column_default as 默认值,
    (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 可空,
col_description(a.attrelid,a.attnum) as 注释
from pg_attribute a
LEFT JOIN information_schema.columns b ON	a.attname=b.column_name
where attstattarget=-1 and attrelid = (select oid from pg_class where relname ='table_name') and b.table_name='table_name'
order by b.ordinal_position asc; -- 表名

MySQL

select
	COLUMN_NAME 列名,
	DATA_TYPE 字段类型,
	CHARACTER_MAXIMUM_LENGTH 长度,
	IS_NULLABLE 是否为空,
	COLUMN_KEY 字段主键,
	COLUMN_DEFAULT 默认值,
	COLUMN_COMMENT 备注
FROM INFORMATION_SCHEMA.COLUMNS
where table_name = 'table_name' --表名