postgresql层级关系:
- 一个用户可以创建多个db,一个db下可以有多个schema,一个schema下可以创建多张表。
表标识:
通过连接信息获取用户下所有的database:
SELECT datname FROM pg_database WHERE datistemplate = false;
通过db信息获取db下可访问的schemas:
SELECT d.datname||'.'||s.schema_name schemas FROM pg_database d,information_schema.schemata s
通过schema获取所有对应的tables:
SELECT * FROM information_schema.tables where table_catalog='kongchao' and table_schema='public';
通过tablename和schema获取对应的columns:
SELECT * FROM information_schema. COLUMNS WHERE table_schema = 'public' AND TABLE_NAME = 'pri_test';
通过列名获取索引:
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 = 'pri_test';
备忘地址:
http://www.alberton.info/postgresql_meta_info.html#.V3Oz95N9635