Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 53 down vote favorite
14

I am new to Postgres. How can I write a stored procedure that imports data from a CSV file and populates the table?

share|improve this question
2  
Why a stored procedure? COPY does the trick – Frank Heikens Jun 7 '10 at 6:43
I have a user interface that uploads the csv file, to hook up this i need the stored procedure that actually copies the data from the cvs file – vardhan Jun 7 '10 at 6:58
That's what COPY does... – Frank Heikens Jun 7 '10 at 6:59
could you elaborate on how to use the COPY ? – vardhan Jun 7 '10 at 7:10
9  
Bozhidar Batsov already gave you a link to an example, the fine manual could also help: postgresql.org/docs/8.4/interactive/sql-copy.html – Frank Heikens Jun 7 '10 at 7:11

1 Answer

up vote 74 down vote

Take a look at this short article.

Solution paraphrased here:

Create your table:

CREATE TABLE zip_codes 
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision, 
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);

Copy data from your CSV file to the table:

COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV;
share|improve this answer
6  
actually use \copy would do the same trick if you do not have the super user access; it complaints on my Fedora 16 when using COPY with a non-root account. – asksw0rder Oct 15 '12 at 17:07
10  
TIP: you can indicate what columns you have in the CSV using zip_codes(col1, col2, col3). The columns must be listed in the same order that they appear in the file. – David Pelaez Jan 2 at 5:16
1  
it should be DELIMITER without the s. – Edmund Mar 14 at 21:20
Updated. Thanks for noticing, @Edmund! – Bozhidar Batsov May 29 at 7:02
1  
@asksw0rder does \copy have the same syntax? bcoz I'm getting a syntax error with \copy – JhovaniC May 29 at 19:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.