Postgre Sql 写入Json字段数据_pgsql insert into json-优快云博客

本文链接：https://blog.youkuaiyun.com/u013931660/article/details/80733421

本文介绍了如何在PostgreSQL中处理JSON字段数据，通过复杂的SQL语句实现数据的写入和操作，尽管代码长度达到200行，使得理解起来具有一定挑战。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

写了个200行的SQL没人看得懂。

update nash_invoice set invoice_express='{"express_type":"","express_status":"","express_number":""}'; // 更新json字段数据

delete from nash_invoice;  // 清空表数据

// 写入表
INSERT INTO nash_invoice(
    id,
    invoice_no,
    contract_no,
    contract_type,
    customer_name,
    nash_company,
    opening_type,
    invoice_type,
    invoice_base_info,
    invoice_open_status,
    invoice_other_info,
    created_by,
    confirm_at,
    created_at,
    updated_at,
    invoice_at,
    apply_at
) 
SELECT
    t.id,
    invoice_no,
    contract_no,
    contract_type,
    relate_table_name,
    COALESCE(c.company_id,s.company_id) as company_id,
    CASE WHEN pay_type = 1 THEN 1
         WHEN pay_type = 2 THEN 2
         WHEN pay_type = 3 THEN 1
         WHEN pay_type = 4 THEN 2
    END AS opening_type,
    invoice_type AS invoice_type,
    COALESCE(c.invoice_base_info,s.invoice_base_info) as invoice_base_info,
    CASE WHEN state = 3 THEN 3
    END AS invoice_open_status,
    w.invoice_other_info,
    create_user_id AS created_by,
    CASE WHEN confirm_date = '' THEN NULL
        WHEN confirm_date = '0000-00-00' THEN NULL
        ELSE confirm_date :: TIMESTAMP
        END AS confirm_at,
    CASE WHEN pass_time = '' THEN NULL
        WHEN pass_time = '0000-00-00 00:00:00' THEN NULL
        ELSE pass_time :: TIMESTAMP
        END