我有一个postgres sql:
cur.execute(f"""INSERT INTO patients_patient (patient_id, patient_name) VALUES
(1,'Jane'),(2,'John')""")
我的问题是,如果数据已经存在,如何使其不插入?
我试过冲突IGNORE:
cur.execute(f"""INSERT INTO patients_patient (patient_id, patient_name) VALUES
(1,'Jane'),(2,'John') ON CONFLICT IGNORE""")
但不起作用。
有朋友可以帮忙吗?
您可以使用以下查询手动检查来插入数据:
WITH ins AS (
SELECT * FROM (VALUES (1,'Jane'),(2,'John')) AS t(patient_id, patient_name)
)
insert into patients_patient (patient_id, patient_name)
select t1.patient_id, t1.patient_name from ins t1
left join patients_patient t2 on t2.patient_id = t1.patient_id
where t2.patient_id is null;