Copy from csv with default values

Question

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.

klin · Accepted Answer · 2016-07-05 09:15:52Z

1

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

edited Jul 5, 2016 at 9:15

answered Jul 4, 2016 at 20:43

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1697111 Over a year ago

I find that by myself. But it is not much 'automatic' :)

klin Over a year ago

Ok, I have another proposition ;)

user1697111 Over a year ago

Hm, I see this first time now! It works partially. Columns where all cells are empty are not filled with ''. Can you improve that?

user1697111 Over a year ago

Finally, I have to use Coalesce method for wanted result. Anyway, thanks for helping!

user1697111 Over a year ago

Is there a place in documentation where I can read more about "NULL '<<>>'" expression?

Collectives™ on Stack Overflow

Copy from csv with default values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related