Экспортировать NULL как "" при использовании COPY TO CSV

Question

Через Python сохраняю таблицу из Postgresql в CSV с помощью запроса:

copy ({function}) to stdout with (format csv, delimiter ',', header true, force_quote *);

Где {function} это запрос: select ...

Вызов самого запроса осуществляется через copy_expert:

with db_connection.cursor() as cursor, open(file_path, "w") as file:
    cursor.copy_expert(query, file)

Всё работает, только null в итоговый csv пишутся как пустое значение:

... "1",,,,"9597a9724d4dfd0c40681a0", ...
       ^^^^

Мне же требуется null сохранить как "":

... "1","","","","9597a9724d4dfd0c40681a0", ...
       ^^^^^^^^^^

Если правильно понял, то параметр NULL в функции COPY указывает на то, какие значения при считывании CSV распознать как null.

Имеется ли возможность обратного - при записи CSV null записать как ""

Или только в исходном select идти и для каждой колонки заменять null на пустые строки через COALESCE - т.к. предварительная ручная замена null на пустые строки помогает? Хотелось бы знать, есть ли возможность параметрами COPY настроить требуемое поведение.

UPD: по комментарию @Mike COPY TO тоже может принимать в параметр NULL значения, но не получается передать именно двойные кавычки:

NULL ''           ->  осталось также ,,,
NULL ""           ->  zero-length delimited identifier at or near """"
NULL ''''         ->  null requires a parameter
NULL ''''''       ->  null requires a parameter
NULL '""'         ->  CSV quote character must not appear in the NULL specification
NULL '""""'       ->  CSV quote character must not appear in the NULL specification
NULL E'\'\''      ->  изменилось на ,',',
NULL E'\'\'\'\''  ->  изменилось на ,'','',
NULL E'\"\"'      ->  CSV quote character must not appear in the NULL specification
NULL E'\"\"\"\"'  ->  CSV quote character must not appear in the NULL specification

Answer 1

Спасибо @Mike за уточнение, что параметр NULL в COPY TO тоже принимает значения, которые пропишутся в CSV вместо NULL-значений.

Однако, по тестам и буквальному толкованию документации функция COPY в PostgreSQL не может обернуть NULL в символ, определенный как QUOTE, по состоянию документации для PostgreSQL 9.5 - блок "Формат CSV ":

В формате CSV отсутствует стандартный способ отличить значение NULL от пустой строки. В PostgreSQL команда COPY решает это с помощью кавычек. Значение NULL выводится в виде строки, задаваемой параметром NULL, и не заключается в кавычки, тогда как значение не NULL, со строкой, задаваемой параметром NULL, заключается. Например, с параметрами по умолчанию NULL записывается в виде пустой строки без кавычек, тогда как пустая строка записывается в двойных кавычках ("").

По умолчанию QUOTE - это символ двойных кавычек ". При попытке указать их в NULL возникает ошибка:

CSV quote character must not appear in the NULL specification

Например, такой вызов приведет к указанной ошибке:

copy ({function}) to stdout with (format csv, delimiter ',', null '""', header true, force_quote *);

Если определить свой QUOTE, то символ " будет доступен для NULL:

copy ({function}) to stdout with (format csv, delimiter ',', quote '~', null '""', header true, force_quote *);

>>> ... ~1~,"","","",~9597a9724d4dfd0c40681a0~, ...

Но, соответственно, теперь к ошибке будет приводить попытка установки параметра NULL как null '~~'.

Из ситуации 2 выхода:

В исходном SELECT-запросе для каждой колонки, где может оказаться NULL использовать функцию COALESCE, для преобразования NULL в пустые строки - они с опцией FORCE_QUOTE точно обернутся в двойные кавычки:

COALESCE(field, '')  >>>  Поля со значениями NULL преобразуются в ''

Использовать иной, более гибкий метод формирования CSV-файла - не COPY TO + copy_expert. Так, в рамках данного вопроса при переходе на pandas с pd.to_csv(quoting=csv.QUOTE_ALL) все данные и заголовки обернулись в двойные кавычки, в т.ч. и NULL.

БЛОГ НА HUSL

Экспортировать NULL как "" при использовании COPY TO CSV

Ответы (1 шт):