Postgres CSV

Importing CSV in Postgres

Posted by Richard Lucas on Dec 10 2013

Log into the database from the terminal:

1
$ psql database_name

Create a new table:

1
2
3
4
5
6
7
8
9
# CREATE TABLE table_name(column_name data_type, column_name2 data_type, etc);
example:
# CREATE TABLE trips(block_id int,route_id 
text,direction_id int,trip_headsign text,shape_id text,service_id text,trip_id text);
//And then you should see:
CREATE TABLE

Copy the data from a CSV:

1
2
3
4
5
6
7
8
# COPY table_name FROM '/path/to/CSV_file.txt' DELIMITER ',' CSV;
example:
# COPY trips FROM '/home/username/Downloads/data/trips.txt' DELIMITER ',' CSV;
//And then you should see:
COPY XXXX <-number of lines

View the tables and select the table you wish to view:

1
2
# \dt
# SELECT * FROM table_name;

Cheers!