我们都知道命令查询表结构的方法如下:
先登录psql,执行:psql -h 123.123.123.123 database_name -U user_name
登录psql之后,再执行命令:\d owner_name.tablename
下面是通过SQL语句直接查询表结构或者视图的方法:
select a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as type
from pg_catalog.pg_attribute a,pg_catalog.pg_class c, pg_catalog.pg_namespace n
where a.attrelid=c.oid
and c.relname=’table_name’
and a.attnum>0 AND NOT a.attisdropped
and n.oid = c.relnamespace
and n.nspname=’owner_name’
order by a.attnum
from pg_catalog.pg_attribute a,pg_catalog.pg_class c, pg_catalog.pg_namespace n
where a.attrelid=c.oid
and c.relname=’table_name’
and a.attnum>0 AND NOT a.attisdropped
and n.oid = c.relnamespace
and n.nspname=’owner_name’
order by a.attnum
另外分享一个查看建表信息的SQL语句:
SELECT *
FROM pg_catalog.pg_stat_operations a
WHERE objname in (lower(‘tablename’))
and schemaname=lower(‘schemaname’)
and subtype=’TABLE’;
FROM pg_catalog.pg_stat_operations a
WHERE objname in (lower(‘tablename’))
and schemaname=lower(‘schemaname’)
and subtype=’TABLE’;