PostgreSQL schema sql

-- 列举table所有字段
select
"column_name",
"data_type",
"character_maximum_length",
"is_nullable"
from information_schema.columns where table_name ='xxx' order by ordinal_position;


-- 表信息（index、view、sequence、view、materialized view...)
-- 包含所有有column的“表”， 包括index等
select * from pg_class;


-- queryl all tables， material views
SELECT tablename FROM pg_tables  
WHERE tablename NOT LIKE 'pg%'  
AND tablename NOT LIKE 'sql_%'
ORDER BY tablename;  

-- 视图
SELECT viewname FROM pg_views  
WHERE schemaname ='public' 


-- 所有字段的注释
select col_description(attrelid, attnum) from pg_attribute;

-- 表的字段的信息
select * from pg_attribute;


-- find table comment
-- attnum 字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数
SELECT
 A.attname AS NAME,
 A.attnotnull AS NOTNULL,
 format_type ( A.atttypid, A.atttypmod ) AS TYPE,
 col_description ( A.attrelid, A.attnum ) AS COMMENT
FROM
 pg_class AS C,
 pg_attribute AS A 
WHERE
 C.relname = 'xxx' 
 AND A.attrelid = C.oid 
 AND A.attnum > 0;

-- 查询物化视图
select schemaname as schema_name,
       matviewname as view_name,
       matviewowner as owner,
       ispopulated as is_populated,
       definition
from pg_matviews
order by schema_name,
         view_name;