CODEWITHSUNDEEP
mysqldatabasepostgresql
user425367
user425367

Reputation:

Postgres import file that has columns separated by new lines

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

Upvotes: 2

Views: 5842

Answers (3)

Paul Sorensen
Paul Sorensen

Reputation: 31

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.

Upvotes: 3

Craig Ringer
Craig Ringer

Reputation: 324531

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.

Upvotes: 2

Mario Zigliotto
Mario Zigliotto

Reputation: 9025

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

Upvotes: 1

Related Questions