PostgreSQL：查询元数据（表 、字段）信息、库表导入导出命令
2024-10-13 00:58:45
一、查询表、模式及字段信息
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';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
二、会话及锁信息
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=bigdata3、表导入导出
（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=bigdata4、免密配置
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;最新文章
- 日期格式代码出现两次的错误 ORA-01810
- shell脚本的调试技巧
- JSONArray传值的使用小结
- 一些peoplecode小技巧平【二】
- Android WebView的使用方法总结
- 计算序列中第k小的数
- 面试题 46 1+ 2＋3+...+n
- Swarm 如何实现 Failover？- 每天5分钟玩转 Docker 容器技术（98）
- 一个特殊的SQL Server阻塞案例分析
- Tomcat映射虚拟路径到指定磁盘（eclipse）
- 百练1041-反反复复-2016正式C题
- mp3-
- 微信小程序---模版
- C#开发Unity游戏教程之判断语句
- JavaScript的setTimeout()和setInterval()
- 42. Trapping Rain Water(直方图 存水量 hard)
- python学习之路 六 ：装饰器
- autokeras 在windows10下的安装与使用
- Qt移植对USB鼠标键盘、触摸屏的支持
- 微信小程序的那些坑