问题:将 CSV 导入 Postgres 并根据需要更新/替换任何字段

我目前保留一个 CSV 主文件,我经常在其中更新以管理产品列表。

如果我尝试直接导入 CSV 文件,我会收到错误“重复键值违反唯一约束...”目前,我通过删除表中的所有项目并重新导入所有数据来更新我的 Products Postgres 表。

我意识到这不是一个很好的方法。有没有更好的方法来解决这个问题?我目前使用 pgAdmin III 和 PG Commander 客户端。

解答

您可以通过定义一个尝试更新现有记录并仅在没有找到记录时才允许插入的触发器函数来做到这一点。

为此,您当然需要有一个主键或其他标准来唯一标识行。

假设您的表定义如下:

CREATE TABLE TEST(
  id INT PRIMARY KEY, 
  name TEXT, 
  amount INT
);

触发函数可能如下所示:

CREATE OR REPLACE FUNCTION test_insert_before_func()
RETURNS TRIGGER
 AS $BODY$
DECLARE
    exists INTEGER; 
BEGIN

    UPDATE test SET name=new.name, amount=new.amount
    WHERE id=new.id
    RETURNING id INTO exists;

    -- If the above was successful, it would return non-null
    -- in that case we return NULL so that the triggered INSERT
    -- does not proceed
    IF exists is not null THEN
        RETURN NULL;
    END IF;

    -- Otherwise, return the new record so that triggered INSERT
    -- goes ahead
    RETURN new;


END; 
$BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE TRIGGER test_insert_before_trigger
   BEFORE INSERT
   ON test
   FOR EACH ROW
   EXECUTE PROCEDURE test_insert_before_func();

现在,如果我插入一个尚不存在的行,它将被插入:

test=> insert into test(id,name,amount) values (1,'Mary',100);
INSERT 0 1
test=> select * from test;
 id | name | amount
----+------+--------
  1 | Mary |    100
(1 row)

如果我尝试插入具有相同 ID 的行:

test=> insert into test(id,name,amount) values (1,'Mary',200);
INSERT 0 0
test=> select * from test;
 id | name | amount
----+------+--------
  1 | Mary |    200
(1 row)

这次该行被更新而不是插入。

如果我从 CSV 文件加载行,它也同样有效。

但是:您可能没有考虑过的一件事:这不会删除数据库中存在且 CSV 文件中不存在的任何记录。如果你想让它工作,你需要一个更复杂的解决方案——也许是这样的序列:

1. 将 CSV 文件加载到临时表中

2. 从真实表中删除临时表中不存在的所有行。桌子

从测试中删除 id 不在(从 temp 中选择 id);

3. 然后最后从 temp 中插入行。表变成真实表:

INSERT INTO test(id,name,amount) (SELECT id,name,amount FROM temp);

此答案不考虑并发问题,以防其他用户同时更新表。但是,如果您只从 CSV 文件加载,那么这不太可能成为问题。