Postgresql使用WITH插入多个表并忽略错误

Question

我的背景是MySQL，我正在学习PostgreSQL(版本12)。我使用Python 3.7和Psycopg2将查询发送到数据库。我试图将数据插入到多个表中的"级联";道路在下面的示例中，security_id是用于母表d_security_id和子表d_security_ticker之间关系的主键，并且母表中的company_name列为Non duplicate

WITH first_insert AS
(
INSERT INTO d_security_id
(
company_name
)
VALUES 
(
%s
)
ON CONFLICT DO NOTHING
RETURNING security_id
)
INSERT INTO d_security_ticker 
(
ticker,
security_id
)
VALUES 
(
%s, (SELECT security_id FROM first_insert)
)    
ON CONFLICT DO NOTHING

当插入一个重复的company_name时，它会触发一个错误，由ON CONFLICT按预期处理。因此，它返回security_id的None值(如预期的那样)。这就触发了我的第二个插入语句，因为security_id不能为Null(定义为外键，但没有显式定义为not Null，以防这种情况)。从python脚本收到的错误是:

psycopg2.errors.NotNullViolation: null value in column "security_id" violates not-null constraint

这个简单的插入查询在MYSQl中相当于IGNORE的正确方法是什么Tx和best

Answer 1

如何改变第二个插入到这个?

INSERT INTO d_security_ticker (ticker, security_id )
SELECT %s, fi.security_id
FROM first_insert fi
WHERE fi.security_id IS NOT NULL;

Answer 2

如果您将查询更改为此，则不会在重复插入尝试时抛出错误。

WITH first_insert AS (
INSERT INTO d_security_id
(company_name)
VALUES ('the name')
ON CONFLICT DO NOTHING
RETURNING company_name, security_id
)
INSERT INTO d_security_ticker (ticker, security_id) (SELECT company_name, security_id FROM first_insert)
ON CONFLICT DO NOTHING;

这样你只需要替换一次value参数。当条目存在时，select语句不返回任何行，因此在with语句之后没有第二次插入尝试。