问 PosgreSQL复制命令中uuid："“或"null”类型的输入语法无效

EN

假设第二列是 boolean ，第三列是 timestamp ，我以下面的方式再现了您的案例

create table test (col1 varchar, col2 boolean, col3 timestamp, col4 varchar, col5 varchar, col6 varchar, col7 varchar, col8 varchar, col9 varchar)                                  ;                                                                                               

如果现在我用

copy test from STDIN delimiter ',' CSV QUOTE '"' NULL 'null';

传递你提到的字符串

"f6a16ff7-4a31-11eb-be7b-8344edc8f36b","false","2021-01-04 00:00:12.347988","null","null","IS_PUBLIC","null","null","null"

数据被正确地解析。

COPY 1

表中的输出看起来是正确的。

defaultdb=> select * from test;
                 col1                 | col2 |            col3            | col4 | col5 |   col6    | col7 | col8 | col9 
--------------------------------------+------+----------------------------+------+------+-----------+------+------+------
 f6a16ff7-4a31-11eb-be7b-8344edc8f36b | f    | 2021-01-04 00:00:12.347988 | null | null | IS_PUBLIC | null | null | null
(1 row)

您无法使用 COPY 加载该文件，因为 "null" 是用双引号引用的，因此不能用作空占位符--它总是被解释为字符串。

最好的方法是将文件加载到定义为 text 的表中，然后执行以下操作

ALTER TABLE doc ALTER uuidcol TYPE uuid USING CAST(nullif(uuidcol, 'null') AS uuid);

尽管@Abelisto命令有效，但我仍然无法上传一些jsonb行。

但我的文件也有一个.json选项，如下所示：

[
        "c0": "f6a16ff7-4a31-11eb-be7b-8344edc8f36b",
        "c1": false,
        "c2": "2021-01-04 00:00:12.347988",
        "c3": null,
        "c4": null,
        "c5": "IS_PUBLIC",
        "c6": null,
        "c7": null,
        "c8": null
]

因此，我最终编写了这个python脚本，它对我起了作用：

import json
import psycopg2
from datetime import datetime
import uuid
connection = psycopg2.connect(user="admin",
                              password="admin",
                              host="127.0.0.1",
                              port="5432",
                              database="postgres")
cursor = connection.cursor()
def insertLine(line):
    id = uuid.UUID(line['c0']).hex
    removed = bool(line['c1'])
    modified_at = datetime.strptime(line['c2'], '%Y-%m-%d %H:%M:%S.%f')
    root_id = uuid.UUID(line['c3']).hex if line['c3'] else None
    parent_id = uuid.UUID(line['c4']).hex if line['c4'] else None
    acl = json.dumps(line['c5']) if line['c5'] else None
    properties = json.dumps(line['c6']) if line['c6'] else None
    data = json.dumps(line['c7']) if line['c7'] else None
    file_meta = json.dumps(line['c8']) if line['c8'] else None
    record_to_insert = (id, removed, modified_at, root_id,
                        parent_id, acl, properties, data, file_meta)
        postgres_insert_query = """INSERT INTO doc (id, removed, modified_at, root_id, parent_id, acl, properties, data, file_meta) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
        cursor.execute(postgres_insert_query, record_to_insert)
        connection.commit()
        count = cursor.rowcount
    except psycopg2.Error as error:
        print("ERROR:" + str(error))
file = 'table.json'
with open(file) as json_file:
    data = json.load(json_file)
    for p in data:
        insertLine(p)