gpt4 book ai didi

postgresql - Postgres 忽略插入错误并继续

转载 作者:行者123 更新时间:2023-12-02 15:16:42 24 4
gpt4 key购买 nike

我有一个具有以下约束的 PostgeresDB:

  CONSTRAINT "Car_Data_3PM_pkey" PRIMARY KEY ("F_ID", "Date"),
CONSTRAINT "Car_Data_3PM_F_ID_fkey" FOREIGN KEY ("F_ID")
REFERENCES "Bike_Data" ("F_ID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

当我尝试使用以下方法插入多个值时:

INSERT INTO "Car_Data_3PM" ("F_ID","Date","Price_Type","O","H","L","LT","EQ","V","NAD") VALUES (38,'2016-10-02 08:19:40.056679','x',0,0,0,112.145,0,0,112.145),(14,'2016-10-02 08:19:40.056679','x',0,0,0,5476,0,0,5476),(13,'2016-10-02

我收到这个错误:

ERROR: insert or update on table "Car_Data_3PM" violates foreign key constraint "Car_Data_3PM_F_ID_fkey" SQL state: 23503 Detail: Key (F_ID)=(38) is not present in table "Bike_Data".

没有插入行。

我怎样才能让 Postgres 只漏掉约束有问题的行?即插入其中的大部分?

最佳答案

你不能让 Postgres 忽略这些值,但你可以重写你的语句以不插入这些行:

INSERT INTO "Car_Data_3PM" ("F_ID","Date","Price_Type","O","H","L","LT","EQ","V","NAD") 
select *
from (
VALUES
(38,'2016-10-02 08:19:40.056679','x',0,0,0,112.145,0,0,112.145),
(14,'2016-10-02 08:19:40.056679','x',0,0,0,5476,0,0,5476),
... -- all other rows
) as x (id, date, price_type, o, h, l, lt, eq, v nad)
where exists (select 1
from "Bike_Data" bd
where bd."F_ID" = x .id)

关于postgresql - Postgres 忽略插入错误并继续,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39827797/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com