PosgreSQL复制命令中uuid："“或"null”类型的输入语法无效
PosgreSQL复制命令中uuid："“或"null”类型的输入语法无效
提问于 2021-04-08 14:23:49
我从SELECT查询中做了csv备份，现在尝试将它导入数据库。但是我发现了一个错误：
COPY doc FROM '/tmp/doc.csv' DELIMITER ',' CSV HEADER;
ERROR: invalid input syntax for type uuid: "null"如您所见，我的文件中有NULL作为"null"。
这发生在以前为空的可选字段上。
我找到了这个解决方案：https://stackoverflow.com/a/40428667/8443131
但这对我没有用：
COPY doc FROM '/tmp/doc.csv' DELIMITER ',' CSV HEADER QUOTE '"null"' NULL '';
ERROR: COPY quote must be a single one-byte character如何导入此文件？
UPD：我试图用空引号替换空值。
已尝试指挥：
COPY doc FROM '/tmp/null.csv' DELIMITER ',' CSV HEADER QUOTE '"' NULL '';
ERROR: invalid input syntax for type uuid: ""文件的简短版本：
"id","removed","modified_at","root_id","parent_id","acl","properties","data","file_meta"
"f6a16ff7-4a31-11eb-be7b-8344edc8f36b","false","2021-01-04 00:00:12.347988","","","IS_PUBLIC","","",""
"2fdd0b8b-4a70-11eb-99fd-ad786a821574","false","2021-01-04 00:00:06.87298","","","IS_PUBLIC","","",""
"2c6d5fd1-4a70-11eb-99fd-ad786a821574","false","2021-01-04 00:00:07.536212","","","IS_PUBLIC","","",""
"fd645c21-4a6f-11eb-99fd-ad786a821574","false","2021-01-04 00:00:11.892367","","","IS_PUBLIC","","",""
"35c1fc53-4a70-11eb-99fd-ad786a821574","false","2021-01-04 00:00:05.517109","","","IS_PUBLIC","","",""
"35d165a4-4a70-11eb-99fd-ad786a821574","false","2021-01-04 00:00:01.72546","","","IS_PUBLIC","","",""
"fd40806d-4a6f-11eb-99fd-ad786a821574","false","2021-01-04 00:00:09.173726","","","IS_PUBLIC","","",""
"30ba4b45-4a70-11eb-99fd-ad786a821574","false","2021-01-04 00:00:04.655073","","","IS_PUBLIC","","",""创建表：
-- Dumped from database version 13.0 (Debian 13.0-1.pgdg100+1)
-- Dumped by pg_dump version 13.0 (Debian 13.0-1.pgdg100+1)
CREATE TABLE public.doc (
id uuid NOT NULL,
removed boolean,
modified_at timestamp without time zone,
root_id uuid,
parent_id uuid,
acl jsonb,
properties jsonb,
data jsonb,
file_meta jsonb
);
ALTER TABLE ONLY public.doc
ADD CONSTRAINT doc_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.doc
ADD CONSTRAINT fk_document_entity FOREIGN KEY (id) REFERENCES public.main_table(id);
ALTER TABLE ONLY public.doc
ADD CONSTRAINT fk_document_parent FOREIGN KEY (parent_id) REFERENCES public.doc(id);回答 3
Stack Overflow用户
发布于 2021-04-08 15:05:51
假设第二列是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)Stack Overflow用户
发布于 2021-04-08 15:05:31
您无法使用COPY加载该文件，因为"null"是用双引号引用的，因此不能用作空占位符--它总是被解释为字符串。
最好的方法是将文件加载到定义为text的表中，然后执行以下操作
ALTER TABLE doc ALTER uuidcol TYPE uuid USING CAST(nullif(uuidcol, 'null') AS uuid);Stack Overflow用户
发布于 2021-04-09 09:04:53
尽管@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)
try:
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)
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")因此，我想在csv中备份jsonb字段是一种糟糕的做法。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67006034
复制相关文章
相似问题
腾讯云开发者
