项目上用到从MySQL跳转到PostgreSQL,需要查询表字段名、字段类型、主键、备注
调查SQL如下:
SELECT
A.COLUMN_NAME --字段名,
CASE
A.is_nullable
WHEN ‘NO’ THEN
1 ELSE 0
END AS is_required --是否必须,
CASE
WHEN LENGTH ( B.attname ) > 0 THEN
1 ELSE 0
END AS is_pk,
A.ordinal_position AS sort --排序,
B.column_comment --注释,
CASE
A.is_nullable
WHEN ‘NO’ THEN
1 ELSE 0
END AS is_increment,
A.data_type --字段类型
FROM
information_schema.
COLUMNS A LEFT JOIN (
SELECT
pg_attribute.attname,
pg_description.description AS column_comment
FROM
pg_index,
pg_class,
pg_attribute,
pg_description
WHERE
pg_class.relname = ‘ipro_dept_party’
AND pg_index.indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_description.objoid = pg_attribute.attrelid
AND pg_description.objsubid = pg_attribute.attnum
) B ON A.COLUMN_NAME = b.attname
WHERE
A.table_schema = ‘public’
AND A.TABLE_NAME = ‘ipro_dept_party’
ORDER BY
ordinal_position ASC;
PostgreSql 查询表字段名、字段类型、主键、备注
最新推荐文章于 2023-12-18 17:06:41 发布