Invalid input syntax for type uuid: "" or "null" in PosgreSQL copy command

Question

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);

Answer 1

I reproduced your case with the following, assuming the second column is a boolean and the 3rd a timestamp

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

if now i use

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

and pass the string you mentioned

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

The data is parsed correctly

COPY 1

and the output from the table looks correct.

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)