I want to load a csv file into the postgresql database(encoding UTF8)
I was able to do that when both the file and postgres were on my local system.
I used this command from psql :
\copy my_table FROM D:/Data/my_file.csv DELIMITER ',' csv escape '”'
however, now i have to copy this file to a table which is on remote server and am unable to do it with this command as postgres expects the file on the same server as postgres and my file is on local sytem.
I have gone through COPY command in postgres docs but i did not quite get how exactly i can achieve this. Also COPY requires that it be executed by only postgres user and cannot be executed with any other user and hence i used \copy (earlier) as it does not have any such requirement.
Is it possible that I can achieve this with \copy? or if not what can i do ?
The difference is that \copy is a psql command that can deal with files stored on the client. Whereas copy (without the backslash) is a SQL command that indeed can only handle files that are located on the server where Postgres is running.
An alternative would be the COPY statement using "from stdin".
yes i used \copy and not copy as copy requires to execute as super user.
if possible, could you please give me an example how i can use STDIN with COPY in my case. I have gone through the docs but am not clear enough on that.
Sorry i have not so technical background so example would be great help.
In my case, will my local machine act as client to the postgres database on remote machine ,so in that case do i require connection string or something?
You can also use PGAdmin's Import utility to get the data through the client.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
I was able to do it through SQL shell(psql) tool which is a command line tool
I gave all the details of the remote server which it prompted for like Server name, database, port, user and password and after successfully logging in, I used the following command where I gave the path of the file on my local machine, I was done
\copy raw.mytable FROM D:\Data\myfile.txt WITH DELIMITER '|' CSV HEADER
(I needed to skip headers from the file while import hence I used CSV syntax)
Alternatively, if you don't have that requirement, you can simply use:
\copy raw.mytable FROM D:\Data\myfile.txt DELIMITER '|'
Just keeping this note here so that any newbie like me can refer and avoid frustration