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. You will need to insert a column including a PurposeId for all rows of data. You can do this one of two ways:
   • Basic method
     1. Insert a new column (move it to the leftmost column if it isn't already there).
     2. Scroll down to the last row of data, and insert your PurposeId in the last row.
     3. Copy your PurposeId.
     4. Press Ctrl + Shift + upward arrow key to select all the rows with data.
     5. Paste your PurposeId into all of the selected rows.
   • Tableau Add-on (if you haven't installed this add-on you will need to for this method)

     1. Type in the purpose ID as the header for one of the columns (see image):

     2. Select the cell directly beneath the purpose ID and go to the Tableau tab on Excel, and click the "Reshape data" button on the top left where the cursor can be seen in the image below:

     3. Click ok

     4. A new worksheet will pop up with your reshaped data:

     5. Delete the original worksheet. (When you go to save the CSV it only saves the 1st worksheet.)

5. Order the columns as they appear in the table you wish to insert the data into.
6. 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 data 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 data 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