PostgreSQL近期常用的表结构查询语句

查询当前数据库下所有表名

--  查询当前数据库下所有表名
 
SELECT TABLE_NAME 
FROM
 information_schema.tables ist 
WHERE
 ist.table_schema = 'public'

查询当前数据库下所有表名及表所属字段名

-- 查询当前数据库下所有表名及表所属字段名
SELECT
column_name,table_name
FROM
 information_schema.COLUMNS isc 
WHERE
 TABLE_NAME IN ( SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'public' )

查询当前数据库下所有表名及表所属字段名、字段类型

-- 查询当前数据库下所有表名及表所属字段名、字段类型
SELECT
column_name,table_name,udt_name
FROM
 information_schema.COLUMNS isc 
WHERE
 TABLE_NAME IN ( SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'public' )

查询当前数据库下所有表名及表所属字段名、字段类型、字段长度

-- 查询当前数据库下所有表名及表所属字段名、字段类型、字段长度
SELECT
column_name,table_name,udt_name,
 COALESCE ( character_maximum_length, numeric_precision, - 1 ) AS "LENGTHTYPE"
FROM
 information_schema.COLUMNS isc 
WHERE
 TABLE_NAME IN ( SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'public' )

获取当前数据库中的所有主键

SELECT
  pg_attribute.attname,
 CASE
   WHEN LENGTH ( pg_attribute.attname ) > 0 THEN
   1 ELSE 0 
  END AS is_pk 
 FROM
  pg_index,
  pg_class,
  pg_attribute 
 WHERE
  pg_class.oid IN ( SELECT oid FROM pg_class WHERE relname IN ( SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'public' ) ) 
  AND pg_index.indrelid = pg_class.oid 
  AND pg_attribute.attrelid = pg_class.oid 
  AND pg_attribute.attnum = ANY ( pg_index.indkey )

查看当前库中全部字段描述

--  查看当前库中全部字段描述
 SELECT
  isc.TABLE_NAME,
  isc.COLUMN_NAME,
  ( SELECT description FROM pg_catalog.pg_description WHERE objoid = pa.attrelid AND objsubid = pa.attnum ) AS descript 
 FROM
  information_schema.COLUMNS isc
  LEFT JOIN pg_attribute pa ON pa.attname = isc.COLUMN_NAME 
  AND pa.attrelid = ( SELECT oid FROM pg_class WHERE relname = isc.TABLE_NAME ) 
 WHERE
  isc.TABLE_NAME IN ( SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'public' ) 
 ORDER BY
  isc.TABLE_NAME

查看当前库中全部表设计信息

-- 查看当前库中全部表设计信息
SELECT 
 A.COLUMN_NAME AS "NAME",
 A.udt_name AS "DATATYPE",
 COALESCE ( A.character_maximum_length, A.numeric_precision, - 1 ) AS "LENGTHTYPE",
CASE
  WHEN LENGTH ( B.attname ) > 0 THEN
  1 ELSE 0 
 END AS "ISPRIMARYKEY",
 A.TABLE_NAME "TABLE_NAME",
 C.descript "REMARKS" 
FROM
 information_schema.
 COLUMNS A LEFT JOIN (
 SELECT
  pg_attribute.attname,
  pg_class.relname 
 FROM
  pg_index,
  pg_class,
  pg_attribute 
 WHERE
  pg_class.oid IN ( SELECT oid FROM pg_class WHERE relname IN ( SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'public' ) ) 
  AND pg_index.indrelid = pg_class.oid 
  AND pg_attribute.attrelid = pg_class.oid 
  AND pg_attribute.attnum = ANY ( pg_index.indkey ) 
 ) B ON A.COLUMN_NAME = B.attname 
 AND A.TABLE_NAME = B.relname
 LEFT JOIN (
 SELECT
  isc.TABLE_NAME,
  isc.COLUMN_NAME,
  ( SELECT description FROM pg_catalog.pg_description WHERE objoid = pa.attrelid AND objsubid = pa.attnum ) AS descript 
 FROM
  information_schema.COLUMNS isc
  LEFT JOIN pg_attribute pa ON pa.attname = isc.COLUMN_NAME 
  AND pa.attrelid = ( SELECT oid FROM pg_class WHERE relname = isc.TABLE_NAME ) 
 WHERE
  isc.TABLE_NAME IN ( SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'public' ) 
 ) C ON A.TABLE_NAME = C.TABLE_NAME 
 AND A.COLUMN_NAME = C.COLUMN_NAME 
WHERE
 A.table_schema = 'public' 
ORDER BY
 A.TABLE_NAME