PostgreSQL 读取表主键和唯一键的SQL

给定表名, 读取对应的约束字段(主键, 唯一键)

SELECT tc.*
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE tc.table_name = 'table_name'

给定表名, 按MySQL的格式输出表结构描述

SELECT
cc.column_name as field,
cc.data_type,
cc.udt_name,
case
when cc.udt_name = 'varchar' then 'varchar('||character_maximum_length||')'
when cc.udt_name = 'text' then 'varchar(1024)'
when cc.udt_name = 'int8' then 'bigint(11)'
when cc.udt_name = 'int4' then 'int(11)'
when cc.udt_name = 'int2' then 'tinyint(2)'
when cc.udt_name = 'numeric' then 'decimal(10)'
when cc.udt_name = 'float4' then 'decimal(10)'
when cc.udt_name = 'float8' then 'decimal(10)'
when cc.udt_name = 'jsonb' then 'varchar(255)'
when cc.udt_name = 'timestamp' then 'datetime'
end as type,
cc.character_maximum_length,
(
SELECT
case when constraint_type = 'PRIMARY KEY' then 'PRI'
when constraint_type = 'UNIQUE' then 'UNI'
else ''
end
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE tc.table_name = 'press_article'
and c.column_name = cc.column_name
) as Key
FROM information_schema.columns cc
WHERE cc.table_name = 'press_article' order by cc.ordinal_position ASC;

posted on   Milton  阅读(129)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· 【全网最全教程】使用最强DeepSeekR1+联网的火山引擎,没有生成长度限制,DeepSeek本体
历史上的今天:
2018-08-11 Ubuntu18.04和OpenWrt 18.06.0 下使用aria2和BaiduExport处理百度盘下载
2016-08-11 配置Jenkins使用Gitlab的代码库进行构建

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示