To transfer data between a file and a table in postgresql there are two options
COPY my_table FROM '/Users/user/Downloads/test.csv' DELIMITER ',' CSV HEADER;
Or
\COPY my_table FROM '/Users/user/Downloads/test.csv' DELIMITER ',' CSV HEADER;
My question is what is the difference between the two and which one is faster?
The COPY command is executed fully on server side - input/output is related to server side streams. But these streams can be redirected to client side - when you run COPY in a pre-configured environment. \COPY is this case.
\COPY is psql's commands - it can be executed only from psql and it prepares internal environment for a possible read/write from client side streams, and it execute COPY commands.
So difference between COPY and \COPY is minimal. \COPY is COPY executed in different configuration for a possible read/write data from client. The performance should be same - \COPY can be a little bit slower due to network overhead (it is clear), but it should not be significant. In this case the communication protocol should be effective.
STDIN pram to COPY makes it act pretty much like \copy, in case