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: '_geo.csv'

CREATE TABLE <ISO>_geo (
        geo_id VARCHAR NOT NULL, 
        deped_id VARCHAR,
        school_name VARCHAR,
        address VARCHAR, 
        adm0 VARCHAR,
        adm1 VARCHAR,
        adm2 VARCHAR,
        adm3 VARCHAR,
        longitude VARCHAR,
        latitude VARCHAR
);

• Personnel data files: '_personnel.csv'

CREATE TABLE <ISO>_personnel (
        geo_id VARCHAR NOT NULL, 
        year VARCHAR NOT NULL, 
        deped_id VARCHAR,
        total_teacher_male VARCHAR, 
        total_teacher_female VARCHAR, 
        total_teachers VARCHAR,
        total_student_male VARCHAR,
        total_student_female VARCHAR,
        total_student_enrollment VARCHAR
);

• Resource data files: '_resources.csv'

CREATE TABLE <ISO>_resources (
	geo_id VARCHAR NOT NULL, 
	year VARCHAR NOT NULL, 
	deped_id VARCHAR,
	water VARCHAR,
	internet VARCHAR,
	electricity VARCHAR,
	computers VARCHAR,
	disability_infrastructure VARCHAR,
	sanitation_facilities VARCHAR,
	ss_sanitation_facilities VARCHAR,
	handwashing_facilities 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 ',')