Postgresql CSV Import from Command Line

PostgreSQL CSV import from command line

As stated in The PostgreSQL Documentation (II. PostgreSQL Client Applications - psql) you can pass a command to psql (PostgreSQL interactive terminal) with the switch -c. Your options are:

1, Client-side CSV: \copy meta-command

perform the SQL COPY command but the file is read on the client and the content routed to the server.

psql -c "\copy tbname FROM '/tmp/the_file.csv' delimiter '|' csv"

(client-side option originally mentioned in this answer)

2. Server-side CSV: SQL COPY command

reads the file on the server (current user needs to have the necessary permissions):

psql -c "COPY tbname FROM '/tmp/the_file.csv' delimiter '|' csv;"

the DB roles needed for reading the file on the server:

COPY naming a file or command is only allowed to database superusers
or users who are granted one of the default roles
pg_read_server_files, pg_write_server_files, or
pg_execute_server_program

also the PostgreSQL server process needs to have access to the file.

using copy from command to load data to postgres from csv

Use force_null, documented at the same place you already linked.

copy emp from stdin with (format csv, header, force_null (registered_course));

PostgreSQL how to import CSV using \copy when file name has spaces?

• Problem 1: Escaping spaces in filenames.

  • While using a backslash '\' to escape spaces ' ' is something done on Unix/Linux shells when filenames aren't enquoted, it isn't necessary in SQL.

    • It isn't necessary inside most (all?) non-shell-scripting program code as strings are always delimited _{(looking at you, PowerShell)}
    • Also, I note that MySQL and PostgreSQL both support backslash escapes, neither use backslash escapes for space characters.
      
      • PostgreSQL only recognizes backslash escape sequences in E''-style strings, btw.

  • Anyway, simply remove the backslash:

    COPY cad.yield_name
         FROM '/data/cad/base/YIELD  NAME.csv'
         DELIMITER ',' csv header;
    

• Problem 2: File encoding

  • In a comment reply, you wrote that you saw this error:

    ERROR: invalid byte sequence for encoding "UTF8": 0xE9 0x20 0x72

  • PostgreSQL assumes the CSV file uses UTF-8 encoding by default, however the character sequence 0xE9 0x20 0x72 strongly hints the file has ISO-8859-1 encoding.

    • This encoding is kinda obsolete now - you should prompt whoever made it to use UTF-8 instead.

  • So add the ENCODING parameter:

    COPY cad.yield_name
         FROM '/data/cad/base/YIELD  NAME.csv'
         DELIMITER ',' csv header ENCODING 'ISO 8859-1';
    

---

Related Topics