CODEWITHSUNDEEP
postgresql
user1697111
user1697111

Reputation: 199

Copy from csv with default values

I have CSV file to import in PostgreSQL table with many columns. I firstly create a temporary table in which then copy CSV file...

DROP TABLE IF EXISTS mylist;
CREATE TEMP TABLE mylist (a text, b text, c text, d text, e text, f text, 
                      g text, h text, i text, j text, k text, l text,
                      m text, n text, o text, p text, q text, r text, 
                      s text, t text, u text, v text, w text, 
                      CONSTRAINT popisp_pkey PRIMARY KEY(A));

COPY mylist FROM 'C:\temp\popisp1.csv' DELIMITER AS ';' CSV;

This copies data into table but additional problem appear. In fields where is no any data seems that Null is copied but I need an empty string ('').

Is here any way to get empty string where is no data in table cell automatically?
I was try to add DEFAULT '' to every column declaration but without success.

Upvotes: 1

Views: 4930

Answers (1)

klin
klin

Reputation: 121784

Add NULL parameter with a string that for sure does not appear in the file:

COPY mylist FROM 'C:\temp\popisp1.csv' DELIMITER AS ';' NULL '<<>>' CSV;

Per the documentation:

NULL

Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format. You might prefer an empty string even in text format for cases where you don't want to distinguish nulls from empty strings. This option is not allowed when using binary format.

Or use coalesce():

update mylist set
    a = coalesce(a, ''),
    b = coalesce(b, ''),
--    ...
    w = coalesce(w, '');

Upvotes: 1

Related Questions