11

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.

Improve this question
1

3 Answers 3

Reset to default
12

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'
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

: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
run \copy with psql only. if you did - please share a screenshot
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.
'D:/test/test.csv' should be hosted on client and you should run psql from client
yes - I mean you should run psql from same machine where you have `` file
|
8

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.

Improve this answer

3 Comments

@: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: 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.
@MabuKloesen No, it means that COPY from file is restricted to superusers, regardless of the file permissions.
0

If COPY or \COPY doesn't work on your postgres configuration, you can use the psql CMD and input this line :

psql -h localhost -p [port] -U [postgres_user] -d [database_name] -f [dump_location]

for example in my case :

psql -h localhost -p 5432 -U just1 -d local_db -f C:\Users\just1\Downloads\dump-tribuo-202409061438.sql
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.