系统平台：
Microsoft Windows (64-bit) 2012
版本：
5.6.5
1、查看当前所有的表（用户表）
SELECT relname
FROM pg_class
WHERE relname !~ '^(pg_|sql_)'
AND relkind = 'r';
或者
也可以
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema'); |
2、查看用户建立的VIEW
|
SELECT table_name
FROM information_schema.views
WHERE table_schema NOT IN
('pg_catalog', 'information_schema')
AND table_name !~ '^pg_'; |
3、当前数据库的用户
|
SELECT usename FROM pg_user; |
4 列出某个表的字段
SELECT a.attname
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = '表名'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid; |
5 查询表的索引
SELECT relname, indkey
FROM pg_class, pg_index
WHERE pg_class.oid = pg_index.indexrelid
AND pg_class.oid IN (
SELECT indexrelid
FROM pg_index, pg_class
WHERE pg_class.relname='表名'
AND pg_class.oid=pg_index.indrelid
AND indisunique != 't'
AND indisprimary != 't'
); |
查询这个表的那些字段，被建立了索引
SELECT t.relname, a.attname, a.attnum
FROM pg_index c
LEFT JOIN pg_class t
ON c.indrelid = t.oid
LEFT JOIN pg_attribute a
ON a.attrelid = t.oid
AND a.attnum = ANY(indkey)
WHERE t.relname = '表名'
; |
获得当前数据库表的建立索引的语句
SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
schemaname = 'public'
ORDER BY
tablename,
indexname; |
6、系统中指定表建立的约束
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = '表名'; |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69994931/viewspace-2794418/
，如需转载，请注明出处，否则将追究法律责任。
