I’ve made csv-backup from SELECT query and now trying to import it back to the database. But i am getting this error:
COPY doc FROM '/tmp/doc.csv' DELIMITER ',' CSV HEADER;
ERROR: invalid input syntax for type uuid: "null"
As you can see, i have NULL as "null"
in my file.
This happens on the optional field which was empty before.
I found this solution: https://stackoverflow.com/a/40428667/8443131
But it is not working for me:
COPY doc FROM '/tmp/doc.csv' DELIMITER ',' CSV HEADER QUOTE '"null"' NULL '';
ERROR: COPY quote must be a single one-byte character
How do i import this file?
UPD: I tried to replace nulls with empty quotes.
Command tried:
COPY doc FROM '/tmp/null.csv' DELIMITER ',' CSV HEADER QUOTE '"' NULL '';
ERROR: invalid input syntax for type uuid: ""
Short version of file:
"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","","",""
Table creation:
-- 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
Answers
Even though @Abelisto command was working i still wasn't be able to upload some jsonb lines.
But i also had an .json alternative of my file like this:
So i end up in writing this python script which worked for me:
So i guess it is just bad practice to backup jsonb fields in csv.
There is no way you can load this file with
COPY
, since"null"
is quoted with double quotes and consequently cannot be used as NULL placeholder – it is always interpreted as string.The best you can do is to load the file into a table where the respective columns are defined as
text
, then do something likeI reproduced your case with the following, assuming the second column is a
boolean
and the 3rd atimestamp
if now i use
and pass the string you mentioned
The data is parsed correctly
and the output from the table looks correct.