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;
标签:
PostgreSQL
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· 【全网最全教程】使用最强DeepSeekR1+联网的火山引擎,没有生成长度限制,DeepSeek本体
2018-08-11 Ubuntu18.04和OpenWrt 18.06.0 下使用aria2和BaiduExport处理百度盘下载
2016-08-11 配置Jenkins使用Gitlab的代码库进行构建