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
27
28
29
30
31
32
33
34
select b.表名,b.表描述,b.列名,b.类型,b.长度,b.主键约束,b.唯一约束,b.外键约束,b.可否为空,(case when c.column_name is not null then 'Y' else 'N' end) as 索引字段,b.描述
from
(select
(select relname from pg_class where oid=a.attrelid) as 表名,
(select relname ||'–'||(select description from pg_description where objoid = oid and objsubid = 0) from pg_class where oid=a.attrelid) as 表描述,
a.attname as 列名,
format_type(a.atttypid,a.atttypmod) as 类型,
(case when atttypmod-4>0 then atttypmod-4 else 0 end) as 长度,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,
(case when a.attnotnull=true then 'Y' else 'N' end) as 可否为空,
col_description(a.attrelid,a.attnum) as 描述
from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like '%' order by relname))
order by 表描述,attnum) b
left join
(select
	distinct
    t.relname as table_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
)c
on b.表名 = c.table_name and b.列名 = c.column_name;

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
27
28
29
30
31
32
33
34
PGPASSWORD=**** psql -h 10.xx.xx.xx -p 5432 -d **** -U **** -c "copy (select b.表名,b.表描述,b.列名,b.类型,b.长度,b.主键约束,b.唯一约束,b.外键约束,b.可否为空,(case when c.column_name is not null then 'Y' else 'N' end) as 索引字段,b.描述
from
(select
(select relname from pg_class where oid=a.attrelid) as 表名,
(select relname ||'–'||(select description from pg_description where objoid = oid and objsubid = 0) from pg_class where oid=a.attrelid) as 表描述,
a.attname as 列名,
format_type(a.atttypid,a.atttypmod) as 类型,
(case when atttypmod-4>0 then atttypmod-4 else 0 end) as 长度,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,
(case when a.attnotnull=true then 'Y' else 'N' end) as 可否为空,
col_description(a.attrelid,a.attnum) as 描述
from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like '%' order by relname))
order by 表描述,attnum) b
left join
(select
	distinct
    t.relname as table_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
)c
on b.表名 = c.table_name and b.列名 = c.column_name) to './test.csv' WITH csv header delimiter ',' encoding 'UTF8'";