pg PostgreSQL 查询全部表备注，表名称，表字段，主键，索引，以及全部字段的备注，全部索引信息，字段类型

查询所有表名称以及字段含义

relchecks=0 为分区表

select
	c.relname 表名,
	cast (
		obj_description (relfilenode, 'pg_class') as varchar
	) 名称,
	a.attname 字段,
	d.description 字段备注,
	concat_ws (
		'',
		t.typname,
		SUBSTRING (
			format_type (a.atttypid, a.atttypmod)
			from
				'\(.*\)'
		)
	) as 列类型
from
	pg_class c,
	pg_attribute a,
	pg_type t,
	pg_description d
where
	a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
and d.objoid = a.attrelid
and d.objsubid = a.attnum
and c.relname in (
	select
		tablename
	from
		pg_tables
	where
		schemaname = 'public'
	and position ('_2' in tablename) = 0
)
order by
	c.relname,
	a.attnum

批量生成全部表的查询/删除语句sql

select distinct lower(pg_class.relname),pg_attribute.attname as colname,
' SELECT count(1) from '|| lower(pg_class.relname)||' where '||pg_attribute.attname||'>''2021-04-01'' ;',
' delete from '|| lower(pg_class.relname)||' where '||pg_attribute.attname||'>''2021-04-01'' ;'
from pg_constraint 
inner join pg_class on pg_constraint.conrelid = pg_class.oid 
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid 
where pg_attribute.attname ~'createtime'
order by lower(pg_class.relname) 

所有表信息及统计值

SELECT
	c.oid,
	obj_description (c.oid),
	c.relhasoids AS hasoids,
	n.nspname AS schemaname,
	c.relname AS tablename,
	c.relkind,
	pg_get_userbyid (c.relowner) AS tableowner,
	t.spcname AS "tablespace",
	c.relhasindex AS hasindexes,
	c.relhasrules AS hasrules,
	c.relhastriggers AS hastriggers,
	ft.ftoptions,
	fs.srvname,
	c.relacl,
	c.reltuples,
	(
		(
			SELECT
				count (*)
			FROM
				pg_inherits
			WHERE
				inhparent = c.oid
		) > 0
	) AS inhtable,
	i2.nspname AS inhschemaname,
	i2.relname AS inhtablename,
	c.reloptions AS param,
	c.relpersistence AS unlogged
FROM
	pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN (
	pg_inherits i
	INNER JOIN pg_class c2 ON i.inhparent = c2.oid
	LEFT JOIN pg_namespace n2 ON n2.oid = c2.relnamespace
) i2 ON i2.inhrelid = c.oid
LEFT JOIN pg_foreign_table ft ON ft.ftrelid = c.oid
LEFT JOIN pg_foreign_server fs ON ft.ftserver = fs.oid
WHERE
	(
		(c.relkind = 'r' :: "char")
		OR (c.relkind = 'f' :: "char")
	)
AND n.nspname = 'public';

查看所有表名

select tablename 
from pg_tables 
where schemaname='public' 
and position('_2' in tablename)=0;

select * from pg_tables;

查询表的全部索引信息

SELECT
	i.indrelid AS oid,
	ci.relname AS index_name,
	ct.relname AS table_name,
	am.amname,
	i.indexrelid,
	i.indisunique,
	i.indisclustered,
	i.indisprimary,
	i.indkey,
	i.indclass,
	obj_description (indexrelid),
	i.indnatts,
	pg_get_expr (indpred, indrelid, true) AS indconstraint,
	pa.rolname AS owner,
	ts.spcname,
	ci.reloptions,
	i.indoption,
	i.indcollation
FROM
	pg_index i
LEFT JOIN pg_class ct ON ct.oid = i.indrelid
LEFT JOIN pg_class ci ON ci.oid = i.indexrelid
LEFT JOIN pg_namespace tns ON tns.oid = ct.relnamespace
LEFT JOIN pg_namespace ins ON ins.oid = ci.relnamespace
LEFT JOIN pg_tablespace ts ON ci.reltablespace = ts.oid
LEFT JOIN pg_am am ON ci.relam = am.oid
LEFT JOIN pg_depend dep ON dep.classid = ci.tableoid
AND dep.objid = ci.oid
AND dep.refobjsubid = '0'
LEFT JOIN pg_constraint con ON con.tableoid = dep.refclassid
AND con.oid = dep.refobjid
LEFT JOIN pg_roles pa ON pa.oid = ci.relowner
WHERE
	tns.nspname = 'public'
-- AND ct.relname = '查询的表名'
AND conname IS NULL
ORDER BY
	ct.relname,
	ins.nspname,
	ci.relname;

pg字段类型

SELECT
	opc.oid,
	opc.opcname,
	nsp.nspname,
	opc.opcdefault
FROM
	pg_opclass opc,
	pg_namespace nsp
WHERE
	opc.opcnamespace = nsp.oid;

表字段类型

SELECT
	col. table_name,
	col. column_name,
	col.character_maximum_length,
	col.is_nullable,
	col.numeric_precision,
	col.numeric_scale,
	col.datetime_precision,
	col.ordinal_position,
	b.atttypmod,
	b.attndims,
	col.data_type AS col_type,
	et.typelem,
	et.typlen,
	et.typtype,
	nbt.nspname AS elem_schema,
	bt.typname AS elem_name,
	b.atttypid,
	col.udt_schema,
	col.udt_name,
	col.column_default AS col_default,
	col.domain_catalog,
	col.domain_schema,
	col.domain_name,
	b.attfdwoptions AS foreign_options,
	col_description (c.oid, col.ordinal_position) AS comment,
	b.attacl,
	coll.collname
FROM
	information_schema. columns AS col
LEFT JOIN pg_namespace ns ON ns.nspname = col.table_schema
LEFT JOIN pg_class c ON col. table_name = c.relname
AND c.relnamespace = ns.oid
LEFT JOIN pg_attrdef a ON c.oid = a.adrelid
AND col.ordinal_position = a.adnum
LEFT JOIN pg_attribute b ON b.attrelid = c.oid
AND b.attname = col. column_name
LEFT JOIN pg_type et ON et.oid = b.atttypid
LEFT JOIN pg_collation coll ON coll.oid = b.attcollation
LEFT JOIN pg_type bt ON et.typelem = bt.oid
LEFT JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid
WHERE
	col.table_schema = 'public'
-- AND col.table_name = '查询的表名'
ORDER BY
	col.table_name,
	col.ordinal_position;

查看表名和备注

select
	relname as tabname,
	cast (
		obj_description (relfilenode, 'pg_class') as varchar
	) as comment
from
	pg_class c
where
	relname in (
		select
			tablename
		from
			pg_tables
		where
			schemaname = 'public'
		and position ('_2' in tablename) = 0
	);

select * from pg_class;

查看特定表名备注

select
	relname as tabname,
	cast (
		obj_description (relfilenode, 'pg_class') as varchar
	) as comment
from
	pg_class c
where
	relname = '查询的表名';

查看特定表名字段

select
	a.attnum,
	a.attname,
	concat_ws (
		'',
		t.typname,
		SUBSTRING (
			format_type (a.atttypid, a.atttypmod)
			from
				'\(.*\)'
		)
	) as type,
	d.description
from
	pg_class c,
	pg_attribute a,
	pg_type t,
	pg_description d
where
	c.relname = '查询的表名'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
and d.objoid = a.attrelid
and d.objsubid = a.attnum;

PostgreSQL查询表主键及注释内容

SELECT
	string_agg (DISTINCT t3.attname, ',') AS primaryKeyColumn,
	t4.tablename AS tableName,
	string_agg (
		cast (
			obj_description (relfilenode, 'pg_class') as varchar
		),
		''
	) as comment
FROM
	pg_constraint t1
INNER JOIN pg_class t2 ON t1.conrelid = t2.oid
INNER JOIN pg_attribute t3 ON t3.attrelid = t2.oid
AND array_position (t1.conkey, t3.attnum) is not null
INNER JOIN pg_tables t4 on t4.tablename = t2.relname
INNER JOIN pg_index t5 ON t5.indrelid = t2.oid
AND t3.attnum = ANY (t5.indkey)
LEFT JOIN pg_description t6 on t6.objoid = t3.attrelid
and t6.objsubid = t3.attnum
WHERE
	t1.contype = 'p'
AND length (t3.attname) > 0
AND t2.oid = '查询的表名'::regclass
group by
	t4.tablename

获取字段名、类型、注释、是否为空

SELECT
	col_description (a.attrelid, a.attnum) as comment,
	format_type (a.atttypid, a.atttypmod) as type,
	a.attname as name,
	a.attnotnull as notnull
FROM
	pg_class as c,
	pg_attribute as a
where
	c.relname = '查询的表名'
and a.attrelid = c.oid
and a.attnum > 0
order by
	a.attname