将postgres表与json数据同步

我想有多种方法可以做到这一点。您可以配置您的表以使 id ON CONFLICT 子句在行已存在时执行更新。

2. 执行更新并找出更新了哪些ID
3. 为未更新的ID执行插入

设置:

CREATE TABLE t (id INTEGER, name TEXT, code TEXT, category TEXT, template TEXT);

INSERT INTO t VALUES (123, 'a name', 'a code', 'a cat', 'a template');

WITH
  source_data AS (
    SELECT (j->>'id')::INTEGER AS id,
           j->>'name' AS name,
           j->>'code' AS code,
           j->>'category' AS category
    FROM JSON_ARRAY_ELEMENTS(
      '[
        {
        "id": 123,
        "name": "a name",
        "code": "123SDF",
        "category": "some category"
        },
        {
        "id": 234,
        "name": "a another name",
        "code": "234SDF",
        "category": "some category"
        }
      ]'::JSON) j
  ),
  updated AS (
    UPDATE t
    SET name = s.name,
        code = s.code,
        category = s.category
    FROM source_data s
    WHERE t.id = s.id
    RETURNING t.id
  )
INSERT INTO t
SELECT id, name, code, category, NULL
FROM source_data s
WHERE s.id NOT IN (SELECT id FROM updated);

做某事的结果 SELECT * FROM t 插入后:

| id  | name           | code   | category      | template   |
| --- | -------------- | ------ | ------------- | ---------- |
| 123 | a name         | 123SDF | some category | a template |
| 234 | a another name | 234SDF | some category |            |

您可以看到现有记录的模板值未被覆盖。