PostgreSQL - 查询表结构和索引信息

1

\d t_student

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

select col.table_schema, col.table_name, col.ordinal_position, col.column_name, col.data_type, col.character_maximum_length, col.numeric_precision, col.numeric_scale, col.is_nullable, col.column_default, des.description from information_schema.columns col left join pg_description des on col.table_name::regclass = des.objoid and col.ordinal_position = des.objsubid where table_schema = 'public' and table_name = 't_student' order by ordinal_position;

1 2

select * from information_schema.columns where table_schema='public' and table_name='t_student';

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

select A.SCHEMANAME, A.TABLENAME, A.INDEXNAME, A.TABLESPACE, A.INDEXDEF, B.AMNAME, C.INDEXRELID, C.INDNATTS, C.INDISUNIQUE, C.INDISPRIMARY, C.INDISCLUSTERED, D.DESCRIPTION from PG_AM B left join PG_CLASS F on B.OID = F.RELAM left join PG_STAT_ALL_INDEXES E on F.OID = E.INDEXRELID left join PG_INDEX C on E.INDEXRELID = C.INDEXRELID left outer join PG_DESCRIPTION D on C.INDEXRELID = D.OBJOID, PG_INDEXES A where A.SCHEMANAME = E.SCHEMANAME and A.TABLENAME = E.RELNAME and A.INDEXNAME = E.INDEXRELNAME and E.SCHEMANAME = 'public' and E.RELNAME = 't_student';

1 2 3 4 5 6 7 8 9 10 11 12

select n.nspname, relname from pg_class c, pg_namespace n where c.relnamespace = n.oid and nspname = 'public' and relkind = 'r' order by relname;