Menu
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?
256
The PostgreSQL \copy command is a meta-command available from the psql interactive client tool. You can use \copy to import data into a table on your RDS for PostgreSQL DB instance.
If you COPY data into a table already containing data, the new data will be appended. If you COPY TO a file already containing data, the existing data will be overwritten.
The easiest but the most efficient way to export data from a Postgres table to a CSV file is by using the COPY command. COPY command generates a CSV file on the Database Server. You can export the entire table or the results of a query to a CSV file with the COPY TO command.
To copy a table with partial data from an existing table, users can use the following statement: Syntax: CREATE TABLE new_table AS SELECT * FROM existing_table WHERE condition; The condition in the WHERE clause of the query defines which rows of the existing table will be copied to the new table.
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.
105
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
