5

I need to load the data from the CSV file to table in PostgreSQL. and I'm not a superuser to use the copy command. when i read few topics from the postgreSQL site I came to know abut the \copy command using STDIN and STDOUT.

I have tried with the same but getting errors. what actually I was trying is I have CSV file located in 'D:/test/test.csv' trying to load in tablename:test by using the below copy command

command: \copy test from stdin.

  1. what is exactly STDIN and where I have to assign the file path

  2. And one more doubt do I need to run this command only in psql or i can run this in SQL workbench.

Zoe
  • 27,060
  • 21
  • 118
  • 148
Rajesh
  • 165
  • 1
  • 1
  • 10

2 Answers2

9

1) stdin is standard input - means you have to paste (or type) the data

2) yes \copy is psql meta-command, not SQL, thus can be executed in psql only...

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

also - you don't have to run from stdin, below should work as well:

\copy test from 'D:/test/test.csv'
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • :Thank you for the replay I got the below error An error occurred when executing the SQL command: \copy test from 'D:/test/test.csv' delimiter ',' CSV ERROR: syntax error at or near "\" Position: 1 \copy test from 'D:/test/test.csv' delimiter ',' CSV ^ 1 statement failed. Execution time: 0.01s – Rajesh Jan 17 '18 at 07:29
  • run `\copy` with `psql` only. if you did - please share a screenshot – Vao Tsun Jan 17 '18 at 08:05
  • I have ran by using psql and i got the ERROR: No such file or directory NOTE: I have my file in local machine and PostgreSQL is hosted in server. – Rajesh Jan 17 '18 at 09:26
  • `'D:/test/test.csv'` should be hosted on client and you should run `psql` from client – Vao Tsun Jan 17 '18 at 09:29
  • You mean in local machine or where the DB is hosted – Rajesh Jan 17 '18 at 09:30
  • 1
    yes - I mean you should run `psql` from same machine where you have `` file – Vao Tsun Jan 17 '18 at 09:32
  • Yes I have Done the same. I have ran the cmd(\copy test from 'D:/test/test.csv' delimiter ',' CSV) in psql from the same machine where i have file.Sitll the same error – Rajesh Jan 17 '18 at 09:36
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/163312/discussion-between-vao-tsun-and-rajesh). – Vao Tsun Jan 17 '18 at 09:40
  • This is working only when my file and DB or on the same machine – Rajesh Jan 17 '18 at 09:40
7

The command COPY is useful for you when you bulk loading a large structured data into database. With my experience we have some thing be noticed here.

  1. STDIN used when your command is after pipeline of other commands such as: CAT xyz.csv | psql -U postgres -c "COPY test FROM STDIN" This command, as I know only run in commandline.

  2. You can load data from file with the syntax COPY test FROM '/tmp/xyz.csv' This command can be run in both psql and pgAdmin, but please attention that the path to the file must be on the server or somewhere that server can reach, and also the privilege of the system user (which run database daemon) can access and read the file.

You can find more information here.

Hopefully this answer will help you.

Mabu Kloesen
  • 1,248
  • 7
  • 8
  • @:Mabu Kloesen: Thanks for the Replay, but my file exists in another server. And when I ran the above command it is showing access denied you must be superuser – Rajesh Jan 17 '18 at 06:39
  • @Rajesh: that means your database server can not access the file on another server with the user which run database daemon. You can try to use and understand it by COPY data from a table into a file then load it into another file to check and understand. – Mabu Kloesen Jan 17 '18 at 07:35
  • @MabuKloesen No, it means that `COPY` from file is restricted to superusers, regardless of the file permissions. – Laurenz Albe Jan 17 '18 at 07:52