【17/26】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_schema = 'public' )
WHERE
 TABLE_NAME IN ( SELECT TABLE_NAME FROM information_schema.tables

-- 查询当前数据库下所有表名及表所属字段名、字段类型、字段长度
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