Which delimiter to use when loading CSV data into Postgres?

Question

I've come across a problem with loading some CSV files into my Postgres tables. I have data that looks like this:

ID,IS_ALIVE,BODY_TEXT
123,true,Hi Joe, I am looking for a new vehicle, can you help me out?

Now, the problem here is that the text in what is supposed to be the BODY_TEXT column is unstructured email data and can contain any sort of characters, and when I run the following COPY command it's failing because there are multiple , characters within the BODY_TEXT.

COPY sent from ('my_file.csv') DELIMITER ',' CSV;

How can I resolve this so that everything in the BODY_TEXT column gets loaded as-is without the load command potentially using characters within it as separators?

The problem isn't with the load command - it's with the file. CSV doesn't allow commas inside a field (it just breaks up the value to two fields) unless it's protected by quotes ("s). — Mureinik
– Mureinik, Commented Mar 5, 2016 at 18:10

Abelisto · Accepted Answer · 2016-03-05 19:17:10Z

1

Additionally to the fixing the source file format you can do it by PostgreSQL itself.

Load all lines from file to temporary table:

create temporary table t (x text);
copy t from 'foo.csv';

Then you can to split each string using regexp like:

select regexp_matches(x, '^([0-9]+),(true|false),(.*)$') from t;

                              regexp_matches                               
---------------------------------------------------------------------------
 {123,true,"Hi Joe, I am looking for a new vehicle, can you help me out?"}
 {456,false,"Hello, honey, there is what I want to ask you."}
(2 rows)

You can use this query to load data to your destination table:

insert into sent(id, is_alive, body_text)
  select x[1], x[2], x[3] 
  from (
    select regexp_matches(x, '^([0-9]+),(true|false),(.*)$') as x 
    from t) t

answered Mar 5, 2016 at 19:17

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

Which delimiter to use when loading CSV data into Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related