PostgreSQL 查看表结构

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT C
	.relname 表名,
	CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) 名称,
	A.attname 字段,
	concat_ws (
	'',
	T.typname,
	SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' )) AS 列类型,
	d.description 字段备注
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
	tablename 
FROM
	pg_tables 
WHERE
	schemaname = 'public' 
	AND POSITION ( '_2' IN tablename ) = 0;
SELECT
	* 
FROM
	pg_tables;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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;

1
2
3
4
5
6
7

SELECT
	relname AS tabname,
	CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT 
FROM
	pg_class C 
WHERE
	relname = '表名';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

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;