如何避免在Postgresq中插入时显式转换NULL

Question

我正在编写python脚本来将表从MSSQL数据库同步到Postgresql数据库。原作者倾向于使用超宽表，其中有许多区域连续的NULL孔。在

为了提高插入速度，我在execute()之前将大量记录序列化为以下格式的字符串

INSERT INTO A( {col_list} ) 
SELECT * FROM ( VALUES (row_1), (row_2),...) B( {col_list} )

在行序列化期间，无法在python中确定NULL或{}的数据类型。这使工作变得复杂起来。timestamp列、integer列等中的所有NULL值都需要显式类型转换为正确的类型，否则Pg会对此进行抱怨。在

目前，我正在检查dbapiconnection.description属性，并比较每个列的列类型转换代码，并根据需要添加::timestamp之类的类型转换。在

但这感觉很麻烦，因为需要做额外的工作：驱动程序已经将数据从文本转换为正确的python数据类型，现在我必须用这些多个None为column重新进行此操作

有没有更好的方法可以优雅而简单地解决这个问题？在

Answer 1

您可以尝试从数据创建json，然后使用json_populate_record(..)从json创建行集。在

postgres=# create table js_test (id int4, dat timestamp, val text);
CREATE TABLE

postgres=# insert into js_test
postgres-# select (json_populate_record(null::js_test,
postgres(# json_object(array['id', 'dat', 'val'], array['5', null, 'test']))).*;
INSERT 0 1

postgres=# select * from js_test;
 id | dat | val
  +  -+   
  5 |     | test

您可以使用json_populate_recordset(..)一次性对多行执行相同的操作。只需传递json值，这是json的数组。确保它不是json数组。在

这样就可以了：'[{"id":1,"dat":null,"val":6},{"id":3,"val":"tst"}]'::json

这不是：array['{"id":1,"dat":null,"val":6}'::json,'{"id":3,"val":"tst"}'::json]

^{pr2}$

Answer 2

如果您不需要SELECT，请使用@Nick's answer。
如果您需要它（比如CTE多次使用输入行），那么根据用例的细节，有一些变通方法。在

例如，在处理完整行时：

INSERT INTO A   complete rows
SELECT * FROM (
   VALUES ((NULL::A).*), (row_1), (row_2), ...
   ) B
OFFSET 1;

{col_list}在这种特殊情况下是可选的噪声，因为我们无论如何都需要提供完整的行。在

详细说明：

• Casting NULL type when updating multiple rows

Answer 3

不必从SELECT插入，您可以直接将VALUES子句附加到INSERT，即：

INSERT INTO A ({col_list}) 
VALUES (row_1), (row_2), ...

当您从查询中插入时，Postgres在试图推断列类型时隔离检查查询，然后尝试强制它们与目标表匹配（结果发现它无法匹配）。在

当您直接从VALUES列表插入时，它在执行类型推断时知道目标表，然后可以假定任何非类型化的NULL与相应的列相匹配。在