Home

1. What you will find here

2. Useful Database Commands

2.1 Manually upload missing data to database:

1. Once you have a CSV containing the missing data, you will need to reformat it to be able to upload it to the database.
2. Open the CSV in Excel
3. Delete any unnecessary columns
4. Type in the purpose ID as the header for one of the columns
5. Select the cell directly beneath the purpose ID and go to the Tableau menu, and click the "Reshape data" button (if you haven't installed this add-on you will need to)
6. Click ok
7. A new worksheet should pop up with your reshaped data, so delete the original worksheet. (When you go to save the CSV it only saves the 1st worksheet.)
8. Order the columns as they appear in the table you wish to insert the data into.
9. Save the file

2.1.1 Option 1 (SQL shell):

1. Open SQL shell
2. Press enter to get into LocalHost
3. Type the database name
4. Enter the port number from Local Forwarding.
5. Type in your Putty username
6. Use the \copy command to upload your CSV (see example below):

\copy tablename (column1, column2, column3) from 'C:\Users\Kat\Desktop\filename.csv' CSV HEADER;

1. Your file is now uploaded to the database!

2.2.2 Option 2 (WinSCP):

1. Open winSCP
2. Find the folder that you want to put your file in, and place your file in the folder(?).
3. Once the file is in the folder, copy the file address to use in the \copy command that you will use in the next steps [You can right click the file, mouse over File Names, and select "Copy to Clipboard (include paths)"]
4. Putty into the server
5. Type the following: psql databasename (type the name of the database that you want to upload the data to)
6. Paste the following (*make sure the file address that you previously copied is from the SERVER, NOT your computer):

\copy tablename (column1, column2, column3) from '/home/kathryn/practice_upload/filename.csv' CSV HEADER;

1. Your file is now uploaded to the database!

2.2 Roles

• Add user to role
  • GRANT admin to joe;
• Create role with NOINHERIT (so it can be granted to other users without inheriting from anything else)
  • CREATE ROLE admin NOINHERIT;

2.3 Permissions

• Alter permissions table-wise:
  • https://www.postgresql.org/docs/9.5/static/sql-grant.html
• Alter default permissions table-wise:
  • https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html