时间戳

首页 > 解决方案 > 向 PostgreSQL 插入和更新数据的一些问题

sql - 向 PostgreSQL 插入和更新数据的一些问题

问题描述

我的任务是将数据从一个模式复制到 PostgreSQL 中同一数据库的另一个模式：

Insert :

Load table incrementally using project_id as a lookup. Insert project record into this table when project_id does not exist.

Update :

If project_id already exists and the value of other attributes is changed, then update only the changed values for the collect project (project_id).

我不太明白请求应该是什么。我的基本是这样的：

INSERT INTO target_schema.supplies select * from source_schema.supplies

但有一个错误：

2021-08-10 13:05:23,482] {{taskinstance.py:1128}} ERROR - duplicate key value violates unique constraint "supplies_pkey"
DETAIL:  Key (id)=(2) already exists.
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/airflow/models/taskinstance.py", line 966, in _run_raw_task
    result = task_copy.execute(context=context)
  File "/usr/local/lib/python3.7/site-packages/airflow/operators/python_operator.py", line 113, in execute
    return_value = self.execute_callable()
  File "/usr/local/lib/python3.7/site-packages/airflow/operators/python_operator.py", line 118, in execute_callable
    return self.python_callable(*self.op_args, **self.op_kwargs)
  File "/usr/local/airflow/dags/local_copy_source_schema_to_target_schema.py", line 31, in copy_data_from_source_schema_to_target_schema
    redshift_hook.run(sql_queries)
  File "/usr/local/lib/python3.7/site-packages/airflow/hooks/dbapi_hook.py", line 175, in run
    cur.execute(s)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "supplies_pkey"
DETAIL:  Key (id)=(2) already exists.

以及未满足任务的其他条件。

标签： sqlpostgresql

解决方案

您应该尝试 ON CONFLICT。

INSERT INTO users (id, level)
VALUES (1, 0)
ON CONFLICT (id) DO UPDATE
SET level = users.level + 1;

取自SO

推荐阅读