Insert data using COPY

suggest change

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

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents