postgresqlcsvpostgresql-copy

COPY function in PostgreSQL

I would like to use the COPY function in PostgreSQL to import a CSV file into a PostgreSQL database.

Where it says the filename in the documentation, does the CSV file have to be stored in a specific location or can it be stored in any location.

For example, copy data_table from '/tmp/outputdata.csv' WITH DELIMITER AS ',' CSV QUOTE AS '"';. Where it says tmp, does that mean the tmp folder in the C: drive. Can it be change to another folder name?

Solution

  • It looks like you are confused by Linux vs. Windows file-path notation. What you have there is a Linux path anchored to root. Windows uses drive letters, which you can specify just as well when you are running on Windows.

    If you use Windows notation and still run with standard_conforming_strings = off, take care to escape backslashes:

    COPY data_table from E'C:\\tmp\\outputdata.csv' WITH ...

    With standard_conforming_strings = on (default since Postgres 9.1) simply write:

    COPY data_table from 'C:\tmp\outputdata.csv' WITH ...

    A PostgreSQL Windows server also understands default path notation with slashes instead of backslashes.

    For SQL COPY FROM / TO you can use any path from which the owner of the server process (postgres by default) has permission to read / write.

    For \copy in the psql client permissions of current local user apply.