Uploading a CSV to the PostgreSQL Database

Once your pull request with your cleaned file is approved & merged into the main branch, follow these steps to upload the file to our deployed database.

1. Log into the server:

ssh globaled@globaleducationobservatory.org

You will be prompted for the password - get that from Heather.

2. Once logged in, log into the database:

psql -U globaled -d globaled_geo

You'll be prompted for the password again.

3. Use one of the below SQL statements to create the table in the database:

• Basic data files: '_basic.csv'

CREATE TABLE _basic ( geo_id VARCHAR NOT NULL, deped_id VARCHAR, school_name VARCHAR, address VARCHAR, adm0 VARCHAR, adm1 VARCHAR, adm2 VARCHAR, adm3 VARCHAR );

• Coordinate files: .csv

3. Check for any error messages. If the table has been successfully created, upload the CSV file to the server. On you local machine, run the following line:

scp <path_to_file_on_local_machine> globaled@globaleducationobservatory.org:/home/globaled/data

An example would be:

scp /Users/heatherbaier/Documents/geo_git/files_for_db/ids/tan_basic.csv globaled@globaleducationobservatory.org:/home/globaled/data

4. Once the file is uploaded, go back to you terminal that is logged into the database and run the following line to populate the table you created with the data in the CSV you just uploaded:

\copy <table_name> FROM <path_to_csv_on_server> WITH (FORMAT CSV, HEADER true, DELIMITER ',')

An example would be:

\copy tan_basic FROM /home/globaled/data/tan_basic.csv WITH (FORMAT CSV, HEADER true, DELIMITER ',')