sql - 发生冲突时，如果 PostgreSQL 中的 json 值不同则更新？

Question

如果“raw_data”表中的“info”列与现有的不同，我想更新它。

其中“info”是 JSON 数据类型。

例如，如果“info”数据存在，则不执行任何操作，否则更新。

代码与尝试:

如果我使用下面的代码:

cursor = connection.cursor()

postgres_insert_query = 
"""
INSERT INTO raw_data (Timestamp, IMIE, info) VALUES (%s,%s,%s)
ON CONFLICT (Timestamp)
DO UPDATE SET
info = EXCLUDED.info 
WHERE raw_data.info IS DISTINCT FROM EXCLUDED.info;"""

record_to_insert = (int(timestamp), int(imei), json_file)
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()

错误:

然后我会收到以下错误:

psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

示例/伪代码:

什么都不做案例:

If Current "info" = {someData: 'ON'} 
Existing "info" = {someData: 'ON'} 
Then Do Nothing

正在更新案例:

If Current "info" = {someData: 'OFFFFFFFFFFF'} 
Existing "info" = {someData: 'ON'} 

UPDATE the "info"

Answer 1

您不能将 JSON 值与 = 进行比较或 <> (这是 is distinct from 在后台使用的)。如果您想比较完整 JSON 值，您必须将该列的数据类型更改为 jsonb ，例如以下工作正常。

使用这个示例表:

create table raw_data ("timestamp" timestamp primary key, imie text, info jsonb);

然后你可以运行这个:

INSERT INTO raw_data ("timestamp", imie, info) 
VALUES (timestamp '2019-07-22 00:00:00', '1234', '{"x": 1, "someData": "on"}')
ON CONFLICT ("timestamp")
DO UPDATE 
  SET info = EXCLUDED.info 
WHERE raw_data.info IS DISTINCT FROM EXCLUDED.info;

在线示例:https://rextester.com/IOYXB82745

但看起来您实际上并不想比较完整的 JSON 值，而只是想比较一个键。然后您可以执行以下操作，这也适用于 json :

INSERT INTO raw_data ("timestamp", imie, info) 
VALUES (timestamp '2019-07-22 00:00:00', '1234', '{"x": 1, "someData": "on"}')
ON CONFLICT ("timestamp")
DO UPDATE 
  SET info = EXCLUDED.info 
WHERE raw_data.info ->> 'someData' IS DISTINCT FROM EXCLUDED.info ->> 'someData';

在线示例:https://rextester.com/VML98935