PostgreSQL效率更新

最新推荐文章于 2024-07-18 07:45:03 发布

manitod

最新推荐文章于 2024-07-18 07:45:03 发布

阅读量810

点赞数

分类专栏： PostgreSQL 文章标签： postgresql 数据库 database

本文链接：https://blog.csdn.net/manitod/article/details/121862591

版权

PostgreSQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文介绍了PostgreSQL中两种高效的数据操作方法：1) 批量插入并用ON CONFLICT子句实现重复数据的更新；2) 使用UPDATE语句批量更新特定字段。这些技巧对于大数据处理和维护数据库一致性至关重要。

摘要由CSDN通过智能技术生成

1、批量插入，重复更新

	insert into cg_aoi_task
        (guid, city_code, sfadcode, x, y, zno_code, aoi_name, aoi_type, type, keywords, source, address, task_src, task_status, create_date, aoi_id)
        VALUES
            (,,,),(,,,,)
        ON CONFLICT(guid, task_src, type) DO UPDATE
        SET city_code = EXCLUDED.city_code
        , sfadcode = EXCLUDED.sfadcode
        , x = EXCLUDED.x
        , y = EXCLUDED.y
        , zno_code = EXCLUDED.zno_code
        , aoi_name = EXCLUDED.aoi_name
        , aoi_type = EXCLUDED.aoi_type
        , keywords = EXCLUDED.keywords
        , source = EXCLUDED.source
        , address = EXCLUDED.address
        , task_status = EXCLUDED.task_status
        , create_date = EXCLUDED.create_date
        , aoi_id = EXCLUDED.aoi_id

2、批量更新

	UPDATE cg_aoi_task as a
        set task_status = b.task_status, task_info = b.task_info, send_date = now()
        FROM (VALUES
            (,,,,),(,,,,),(,,,,)
        ) as b(guid, batch_id, task_status, task_info, issue_id)
        where b.guid = a.guid and b.batch_id = a.batch_id and b.issue_id = a.issue_id

3、PG库导数据

_fwd方式
-- 【普通用户】
create user mapping for background_work server server_remote options(user 'background',password 'xxpasswd');
CREATE FOREIGN TABLE t_resource 
(   fd_resid SERIAL,
        fd_res_name TEXT,
        fd_res_desc TEXT,
        fd_tenantid INTEGER, 
        fd_service TEXT, 
        fd_display_name TEXT, 
        fd_modify_date TEXT, 
        fd_modify_person_id INTEGER
)
server pg_66_background 
options (schema_name 'qb_bj',table_name 'aoi_ab_prod'));

_dblink方式
-- 创建链接
select dblink_connect('test_dblink','dbname=pg_bj_aoi host=127.0.0.1 port=5432 user=qb_bj password=qbbj123');

-- 查询连接数据
select * from dblink('test_dblink','select ogc_fid from "qb_bj"."aoi_ab_prod" limit 1000') as t1(ogc_fid int);