PostgreSQL：查询元数据（表 、字段）信息、库表导入导出命令

一、查询表、模式及字段信息

1、查询指定模式下的所有表

select tablename,*
from pg_tables
where schemaname = 'ods';

2、查询指定模式下的表名及字段

SELECT
	C.relname,
	A.attname AS NAME,
	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,
	pg_tables AS B
WHERE A.attrelid = C.oid
  and C.relname=B.tablename
  AND A.attnum > 0
  AND B.schemaname = 'ods'
  AND B.tablename NOT SIMILAR TO '[a-z,_]+_2022[0-9]+';

3、查询包含指定字段的表

SELECT DISTINCT
	C.relname
FROM
	pg_class AS C,
	pg_attribute AS A,
	pg_tables AS B
WHERE A.attrelid = C.oid
  and C.relname=B.tablename
  AND A.attnum > 0
  AND B.schemaname = 'ods'
  AND B.tablename NOT SIMILAR TO '[a-z,_]+_2022[0-9]+'
  AND A.attname ='dt';

上述方式不好用，采用下面的方式

SELECT distinct 
	TABLE_NAME
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  table_schema = 'ods' 
  AND table_name NOT SIMILAR TO '%_2022%'
	and column_name='dt'

4、查询指定表的字典（表名、字段名、备注、类型）

select
  c.relname as 表名,
  obj_description(relfilenode, 'pg_class')::varchar as 表注释,
  d.description as 字段备注,
  a.attname as 字段名,
  concat_ws ('', t.typname,SUBSTRING (format_type (a.atttypid,a.atttypmod) from'\(.*\)')) as 字段类型
from
  pg_class c,
  pg_attribute a,
  pg_type t,
  pg_description d
where a.attnum > 0
	and a.attrelid = c.oid
	and a.atttypid = t.oid
	and d.objoid = a.attrelid
	and d.objsubid = a.attnum
	and c.relname in (select tablename from pg_tables where schemaname = 'ap')
	and c.relname = 'fact_ito'

查询非分区表：and position ('_2' in tablename) = 0

5、查询指定模式下的表名及中文名

select
  c.relname as 表名,
  obj_description(relfilenode, 'pg_class')::varchar as 表注释 -- 什么函数？？？
from pg_class c
join pg_tables d
on c.relname=d.tablename
where c.relname in (select name from tb)
and d.schemaname = 'tp'
order by c.relname asc

6、使用视图查询表名及字段

SELECT distinct TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'ods' 
AND table_name NOT SIMILAR TO '%_20[0-9]+'

二、会话及锁信息

1、查询所有会话

SELECT sa.* FROM pg_catalog.pg_stat_activity sa

三、导入导出命令

1、数据库导入导出

（1）从源数据库导出结构

pg_dump --host=172.16.6.219 --port=5432 --username=postgres --dbname=bigdata --if-exists --clean --no-privileges --no-owner --schema-only --file=bigdata_20220815.sql

（2）从源数据库导出结构和数据

sudo pg_dump --host=172.16.6.219 --port=5432 --username=postgres --dbname=kettle --if-exists --clean --no-privileges --no-owner --file=kettle_20221110.sql

 （3）从文件导入目标数据库

sudo psql --host=localhost --port=5432 --username=postgres --file=kettle_20221110.sql --dbname=kettle 

 2、模式导入导出

（1）导出模式结构

sudo pg_dump --host=172.16.6.219 --port=5432 --username=postgres --dbname=bigdata --schema=ods --if-exists --clean --no-privileges --no-owner --schema-only --file=schema_bigdata_ods_20221110.sql

（2）导出模式结构和数据

sudo pg_dump --host=172.16.6.219 --port=5432 --username=postgres --dbname=bigdata --schema=ods --if-exists --clean --no-privileges --no-owner --file=bigdata_ods_20221110.sql

（3）从文件导入模式

sudo psql --host=172.16.6.219 --port=5432 --username=postgres --file=bigdata_ods_20221110.sql --dbname=bigdata

3、表导入导出

（1）导出源表结构

pg_dump --username=postgres  --host=172.16.5.66 --port=5432 --dbname=bigdata --if-exists --clean --schema-only --no-privileges --no-owner --file=dim_area_value.sql  --table=ap.dim_area_value

（2）导出源表数据和结构

pg_dump --username=postgres  --host=172.16.5.66 --port=5432 --dbname=bigdata --if-exists --clean --no-privileges --no-owner --file=dim_area_value.sql  --table=ap.dim_area_value 

（3）从文件导入至目标表

psql --host=112.94.20.4  --port=5432 --username=postgres --file=dim_area_value.sql --dbname=bigdata

 4、免密配置

5、实际执行的命令

（1）导入

COPY ap.analysis_operating_expenses_details (amount, level_one_name, 
                                             level_two_name, level_three_name, 
                                             level_four_name, name, date, 
                                             organization_name, organization_name_short, 
                                             dim_customer_name, customer_type, cus_industry,
                                             staff_name, cus_manage_code) FROM stdin;

（2）导出

COPY ap.fact_ship_20221031 (order_no, order_line_no, produce_no, 
                            ship_no, cus_code, product_code, 
                            sales_area_code, sales_organize_code,
                            sales_scene, ship_quantity, receipt_quantity,
                            receipt_baseline, receipt_baseline_days,
                            forecast_receipt_date, forecast_income_date, 
                            ship_date, production_cost_baseline, price,
                            receipt_overdue_flag, receipt_overdue_level,
                            income_rule, dt, warehouse_code, staff_code,
                            contract_type, confirm_rule, material_cost_baseline, 
                            artificial_cost_baseline, manufacture_cost_baseline,
                            cus_biz_no, not_tax_price, not_tax_authorize_price,
                            ship_row) TO stdout;