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 the file name is indicated in the documentation , the CSV file must be stored in a specific location or can be saved anywhere.

For example, copy data_table from '/tmp/outputdata.csv' WITH DELIMITER AS ',' CSV QUOTE AS '"'; . Where it says tmp , does this mean a tmp folder on drive C :. Can I change the name of another folder?

Answer 1

It seems that you are confused by Linux or Windows, designating the path to the file. What you have is the root path of Linux. Windows uses drive letters, which you can also specify when using Windows.

If you use Windows notation, take care to avoid backslashes if you do not use standard_conforming_strings = on - by 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 the PostgreSQL server for Windows also understands the default path entry with a slash instead of a backslash.

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

For the psql client \copy meta-command, the permissions of the current local user are applied.

Answer 2

Yes, of course, you can specify any place where you have read access. No problem changing the file path.

Just note that on windows you should avoid backslash this way:

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