CODEWITHSUNDEEP
sqlpostgresql
Rajesh
Rajesh

Reputation: 215

PostgreSQL copy command using STDIN

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.

Upvotes: 10

Views: 46284

Answers (3)

Bahaha_JUST'1
Bahaha_JUST'1

Reputation: 1

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

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51529

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'

Upvotes: 11

Mabu Kloesen
Mabu Kloesen

Reputation: 1358

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.

Upvotes: 8

Related Questions