2

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

Improve this question
3
  • '\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). Commented Aug 23, 2013 at 4: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. Commented Aug 23, 2013 at 4:56
  • any pointers , suggestions stackoverflow.com/questions/45414536/… Commented Jul 31, 2017 at 14:11

3 Answers 3

Reset to default
3

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.

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

1 Comment

stackoverflow.com/questions/45414536/…
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.

Improve this answer

3 Comments

The COPY statement throws an ERROR: COPY delimiter cannot be newline or carriage return
@user637338 Um, yes. Read the whole answer.
any suggestions stackoverflow.com/questions/45414536/…
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.