Importing CSVs to postgreSQL database

Importing CSVs to postgreSQL database

All the CSVs for this project are in a Google Drive folder called "FAIRPLAY_csv_data.zip" and is organized by model/table.

From the command line (CLI), get into your postgres database by doing the following

1. Type psql

2. and then: \c fairplay; to actually get into the fairplay database

3. Once there, you will be able to see the data available to you by doing any of the following:

   • \d; allows you to see all the tables in the database (which include all the corresponding models: webapp_district, webapp_gradeenrollment, webapp_school, and webapp_sportsenrollment)
   • \d; table_name (for example, webapp_district) allows you to see the fields that compose a table
   • select * from table_name; allows you to see what's inside the fields in a table (the actual data)

To copy data from CSV to postgres:

1. (from still inside psql and fairplay database): copy webapp_district from '/Users/lindsaysilver/Desktop/FAIRPLAY/FAIRPLAY_models_data/District_model/districtAB.csv' DELIMITERS ',' CSV HEADER;
   • another example: copy webapp_gradeenrollment from /Users/lindsaysilver/Desktop/FAIRPLAY/FAIRPLAY_models_data/GradeEnrollment_model/enrollAB2012.csv' DELIMITERS ',' CSV HEADER;

To alter an integer field if number is too large

I kept having an issue with all our composite_id fields being too big, and getting this error:

ERROR: integer out of range

To remedy this, type the following:

• alter table webapp_school alter column composite_id type bigint; or alter table webapp_sportsenrollment alter column school_id type bigint;

Dumping postgres database to file

Type the following:

• pg_dump fairplay > dbexport.pgsql or more generically: pg_dump database_name > outname.file_format