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;