The message introduces the PostgreSQL copy command, which uses examples to export the query results to the csv file and import the data file to postgresql.
1. Introduction to copy command
The copy command is used to transfer data directly on the postgreSql table and standard file system. The copy command allows the PostgreSQL server to read and write files directly, so the files must be accessible to PostgreSQL users. The file used by this command is a file that is read and written directly by the database server, not a file that is applied by the client, so it must be located local to the server or directly accessed, rather than the client location.
copy toThe command copy the contents of the table to the file, and you can also copy the query results of select; that is, if some fields are listed after select, thencopy toThe command copies only the result of the specified field to the file. Ready to usecopy toThe table of the command must be granted select permission. This command can only be used on tables and cannot be used in views; when we need to copy the view content, we can pass the SQL query to the copy command:
COPY (SELECT * FROM country) TO ‘list_countries.copy';
copy fromCopy the file's data to the data table. When usingcopy from, each field in the file is inserted into a specific field in an i order. Get their default values if the column parameters of the tables in this command are not specified. usecopy fromThe table of the command must be granted insert permission.
Don't confuse the copy command and the psql\copy。\copyCallCOPY FROM STDIN or COPY TO STDOUT, then return the data or store file data that can be accessed by the psql client. So when using \copy, the accessibility and access rights of the file depend on the client, not the server.
2. Copy command example
2.1 Export tables from Postgresql
We can copy the table to the console and use vertical bar (|) as the field direct separator:
copy customers to stdout(delimiter '|')
Copy the table to the file, using spaces as the field direct delimiter:
COPY customers TO '/database/data/test_data.copy' (DELIMITER ' ');
2.2 Import files to Postgresql table
Pass in the file to the existing table:
COPY customers FROM '/database/data/test_data.copy' (DELIMITER ' ');
2.3 Export query results to file
Export the query results to the file:
COPY (SELECT * FROM customers WHERE name LIKE 'A%') TO '/data/test_data.copy';
If you need to compress data, use the following command:
COPY customers TO PROGRAM 'gzip > /data/test_data.';
If you export only 2 columns, use the following command:
COPY customers(col, col2) TO '/data/test_data.copy' DELIMITER ' ';
If you need to export the binary file:
copy customers to 'e:/' with binary;
Export the csv file:
copy customers to 'e:/' with csv;
There are some other parameters in the csv file:
DELIMITER– The separator that divides each field in the data row. CSSV files generally use commas.
HEADER– Specify the title of the csv file. If the title line is not required, HEADER can be ignored.
COPY country FROM '/data/test_data.copy' WITH delimiter ',' CSV HEADER;
Specify the encoding:
COPY country FROM '/data/test_data.copy' WITH delimiter ',' CSV HEADER encoding 'utf-8';
3. Summary
This article introduces the copy command in PostgreSQL and shows different application scenarios through examples. Loading a large number of rows with COPY is always faster than using INSERT, COPY will set it once, and the overhead for each row is very low, especially if the trigger is not involved.
This is the end of this article about the detailed explanation of the PostgreSQL copy command tutorial. For more related PostgreSQL copy command content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!