COPY function in PostgreSQL

Question

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 1

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, take care that you have to escape backslashes if you are not using standard_conforming_strings = on - which is the default in Postgres 9.1 or later, but not in older versions. Like:

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

With standard_conforming_strings = on you can simply write:

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

Note that a PostgreSQL Windows server also understands default path notation with slashes instead of backslashes.

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

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

Solution 2

Yes, of course you can specify whatever location where you have read access. There's no problem changing the path of the file.

Keep attention only on the fact that on windows you have to escape the backslash in this way :

copy data_table from 'c:\\Temp\\outputdata.csv' WITH DELIMITER AS ',' CSV QUOTE AS '"';

Comments

[Jeiman]

Thank you for the explanation.

[R13e]

Dont use the COPY command with pgAdmin on a Windows machine and try to read a file from your local Windows environment. This will fail. You can import a csv file anyway. Use the GUI to do that. Right click on the desired table and choose the import…