MOCKSTACKS
EN
Questions And Answers

More Tutorials









postgreSQL Insert data using COPY


COPY is PostgreSQL's bulk-insert mechanism. It's a convenient way to transfer data between file sand 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 ',';



Conclusion

In this page (written and validated by ) you learned about postgreSQL Insert data using COPY . What's Next? If you are interested in completing postgreSQL tutorial, your next topic will be learning about: postgreSQL INSERT data and RETURING values.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.