Postgresql查询系统表得到表中列的类型

skypaladin

SELECT
a.attname as "Column",
pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
pg_catalog.pg_attribute a
WHERE
a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(views_ads_date_statics)$'
AND pg_catalog.pg_table_is_visible(c.oid)
);


结果：

Column    |          Datatype
-------------+-----------------------------
user_id     | integer
ad_id       | integer
impressions | bigint
daterange   | timestamp without time zone


详情参见文档：http://www.uudo.net/onlinebook/database/PostgreSQL80zhref/