How Do You Ingest (or Import) a CSV File into a Postgresql Database Table?

Problem scenario
You want to extract data from a .csv file.  In other words you want to  transform data from a flat file and import it into a database.  Is there an example of how to create a table, format a .csv file, and run a command to import the .csv file into the Postgresql database?

Solution
1.  Here is how to create a table to target for the import or ETL (Extract, Transform, Load) process:

CREATE TABLE continualtable (id int CONSTRAINT firstkey PRIMARY KEY, streetAddress varchar(200), city varchar(100), state char(2), zipcode int, phonenumber varchar(20));

2.  Here is the content of a .csv file that will be imported into the database table created with the above command:

id      streetAddress   city    state   zipcode phonenumber
1,123 Broadway,New York,NY,10001,212-555-5555
2,50 Sunset Blvd,Los Angeles,CA, 90210,310-555-5555
3,400 West Jackson Blvd,Chicago,IL,60611,312-555-5555

3.  From a command terminal of the Linux server with Postgresql, run these commands:

sudo -i -u postgres
psql
COPY continualTable FROM '/tmp/file.csv' WITH CSV HEADER; 

For the COPY command example above remember that "continualTable" is the table name of the target table for this import process, "/tmp/" is the path to your file and your file is named file.csv.  

Leave a comment

Your email address will not be published.