Postgres import file that has columns separated by new lines

Question

I have a large text file that has one column per row and I want to import this data file into Postgres.

I have a working MySQL script.

 LOAD DATA LOCAL 
        INFILE '/Users/Farmor/data.sql' 
        INTO TABLE tablename 
        COLUMNS TERMINATED BY '\n';

How can I translate this into Postgres? I've tried amongst other this command.

COPY tablename
    FROM '/Users/Farmor/data.sql' 
    WITH DELIMITER '\n'

However it complains:

ERROR: COPY delimiter must be a single one-byte character

`'\n'` is a two character string (depending on string settings and PostgreSQL version), you want to say `E'\n'` to get a newline. But that will probably get you a "COPY delimiter cannot be newline or carriage return" error as PostgreSQL won't be able to tell the difference between columns and rows if they both use the same delimiter. I'd guess that you're going to have to mangle your data file into some other layout (possibly even by loading it into MySQL and then dumping it from there to SQL or CSV). — mu is too short, Aug 23 '13 at 04:32
You were correct. I will take the dump approach or write a perl script. Strange really that Postgres can't figure it out, a 10 column table will have a new row every other 10 lines. Make your comment an answer so I can accept it. — , Aug 23 '13 at 04:56
any pointers , suggestions https://stackoverflow.com/questions/45414536/multiple-column-copy-format-postgresql-node-js — Rizwan Patel, Jul 31 '17 at 14:11

score 2 · Answer 1 · answered Aug 23 '13 at 08:39

2

The immediate error is because \n is just a two char string, \ and n.

You want:

COPY tablename
    FROM '/Users/Farmor/data.sql' 
    WITH DELIMITER E'\n'

The E'' syntax is a PostgreSQL extension.

It still won't work, though, because PostgreSQL's COPY can't understand files with newline column delimiters. I've never even seen that format.

You'll need to load it using another tool and transform the CSV. Use an office suite, the csv module for Python, Text::CSV for Perl, or whatever. Then feed the cleaned up CSV into PostgreSQL.

answered Aug 23 '13 at 08:39

Craig Ringer

307,061
76
688
778

The COPY statement throws an ERROR: COPY delimiter cannot be newline or carriage return – user637338 Apr 27 '15 at 15:06
@user637338 Um, yes. Read the whole answer. – Craig Ringer Apr 27 '15 at 22:00
any suggestions https://stackoverflow.com/questions/45414536/multiple-column-copy-format-postgresql-node-js – Rizwan Patel Jul 31 '17 at 14:11

score 2 · Answer 2 · answered Sep 16 '14 at 17:24

2

While postgresql will not recognize \n as a field delimiter, the original question asked how to import a row as a single column and this can be accomplished in postgresql by defining a delimiter not found in the data string. For example:

COPY tablename
FROM '/Users/Farmor/data.sql' 
WITH DELIMITER '~';

If no ~ is found in the row, postgresql will treat the entire row as one column.

answered Sep 16 '14 at 17:24

Paul Sorensen

21
1

https://stackoverflow.com/questions/45414536/multiple-column-copy-format-postgresql-node-js – Rizwan Patel Jul 31 '17 at 14:11

score 1 · Answer 3 · edited May 23 '17 at 12:28

1

Your delimiter is two characters so it's a valid error message.

I believe the simplest approach would be to modify the file you're importing from and actually change the delimiters to something other than \n but that might not be an option in your situation.

This question addresses the same issue: ERROR: COPY delimiter must be a single one-byte character

edited May 23 '17 at 12:28

Community

1
1

answered Aug 23 '13 at 04:35

Mario Zigliotto

8,315
7
52
71

Postgres import file that has columns separated by new lines

3 Answers3