查询指定schema下面的表
select '|' || t.tablename as tab_name,
coalesce(replace(obj_description((t.SCHEMANAME||'."'||t.TABLENAME||'"')
::regclass),
'|',
''),'') || '|' AS tab_desc
from pg_tables t
where t.schemaname = 'dcf_user'
and not exists (select 'x'
from pg_partitions s
where s.partitiontablename = t.tablename);
查询指定的表下面的列信息
SELECT '|'|| aa.COL_NAME AS column_name,
(CASE aa.COL_TYPE
WHEN 'character' THEN
'char'
WHEN 'character varying' THEN
'varchar'
WHEN 'timestamp without time zone' THEN
'timestamp'
ELSE
aa.COL_TYPE
END) AS data_type,
aa.COL_LEN AS data_length,
aa.COL_PREC AS data_precision,
bb.COL_DESC AS comments,
(CASE aa.COL_NULLABLE
WHEN 'NO' THEN
'0'
ELSE
'1'
END) AS nullable,
(CASE COALESCE(CC.COLUMN_NAME,'0')
WHEN '0' THEN
'0'
ELSE
'1'
END) || '|' AS key_flag
FROM (SELECT TABLE_NAME,
A.ORDINAL_POSITION AS COL_NUM,
A.COLUMN_NAME AS COL_NAME,
A.DATA_TYPE AS COL_TYPE,
CASE WHEN A.DATA_TYPE='numeric' THEN A.NUMERIC_PRECISION
ELSE A.CHARACTER_MAXIMUM_LENGTH END COL_LEN,
A.NUMERIC_SCALE AS COL_PREC,
A.DATETIME_PRECISION AS COL_TIME_PREC,
A.IS_NULLABLE AS COL_NULLABLE,
A.COLUMN_DEFAULT AS COL_DEFAULT,
A.TABLE_SCHEMA AS TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE A.TABLE_SCHEMA = 'dcf_user'
AND A.table_name = 'a_rkcj') AS AA
LEFT JOIN (SELECT t2.RELNAME table_name,
t1.OBJSUBID,
t1.DESCRIPTION AS COL_DESC
FROM PG_DESCRIPTION t1
INNER JOIN PG_STAT_ALL_TABLES t2 ON t1.OBJOID = t2.RELID
WHERE t2.SCHEMANAME = 'dcf_user'
AND T2.RELNAME = 'a_rkcj') AS BB ON (AA.COL_NUM =
BB.OBJSUBID AND
AA.table_name =
bb.table_name)
LEFT JOIN (select pg_namespace.nspname as table_schema,
pg_class.relname as TABLE_NAME,
pg_attribute.attname as COLUMN_NAME
from pg_constraint
inner join pg_class
on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute
on (pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = pg_constraint.conkey[1])
inner join
pg_namespace
on pg_class.relnamespace=pg_namespace.oid
where pg_constraint.contype = 'p'
and pg_namespace.nspname='dcf_user'
and pg_class.relname='a_rkcj'
) CC ON (AA.TABLE_SCHEMA = CC.TABLE_SCHEMA
AND AA.TABLE_NAME = CC.TABLE_NAME
AND AA.COL_NAME = CC.COLUMN_NAME)
ORDER BY aa.COL_NUM;
