postgresql INSERT Insert data using COPY
Example
COPY is PostgreSQL's bulk-insert mechanism. It's a convenient way to transfer data between files and tables, but it's also far faster than INSERT when adding more than a few thousand rows at a time.
Let's begin by creating sample data file.
cat > samplet_data.csv
1,Yogesh
2,Raunak
3,Varun
4,Kamal
5,Hari
6,Amit
And we need a two column table into which this data can be imported into.
CREATE TABLE copy_test(id int, name varchar(8));
Now the actual copy operation, this will create six records in the table.
COPY copy_test FROM '/path/to/file/sample_data.csv' DELIMITER ',';
Instead of using a file on disk, can insert data from stdin
COPY copy_test FROM stdin DELIMITER ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 7,Amol
>> 8,Amar
>> \.
Time: 85254.306 ms
SELECT * FROM copy_test ;
id | name
----+--------
1 | Yogesh
3 | Varun
5 | Hari
7 | Amol
2 | Raunak
4 | Kamal
6 | Amit
8 | Amar
Also you can copy data from a table to file as below:
COPY copy_test TO 'path/to/file/sample_data.csv' DELIMITER ',';
For more details on COPY you can check here
