Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 12 Next »

4. Postgres Nuts and Bolts

This module explores the building blocks of a Postgres database in greater detail.

4.1. Data Hierarchy

Schemas are the equivalent of folders in a file system, supporting the organisation of other database objects into logical groups (for example planning, education, highways). All tables must be inside a schema, and schemas also hold function and views.

Within a schema, tables store the records or rows which make up your data, and have rows and columns like a spreadsheet.

Tables have columns which define the structure of the data stored in a table, including name, datatype (string, integer, date etc.) and size.

Create Schema and Table

  1. Create a new schema in pgAdmin by navigating in your database to Schemas, then right-click > Create > Schema…

  2. Give the schema a name (for example training) and keep the owner as postgis, then click Save to create it

  3. Now recreate the structure of an existing table in the new schema

  4. Navigate to crime > neighbourhood_crime, and click SQL in the toolbar at the top of the window - you will see the SQL needed to create the table on the right

  5. Copy the SQL from the window on the right, and click the Query Tool button in the toolbar, then paste the SQL into the Query window

  6. Delete the last four statements (TABLESPACE, ALTER TABLE, GRANT ALL)

  7. Edit the references to the crime schema in the SQL to reference your new schema name, then click the Execute/Refresh (Play) button in the toolbar to create the new table

  8. Click on the new table in the new schema, and click the View Data button in the toolbar

  9. In the Data Output window at the bottom, click the Add Row button, then double-click on a cell to add data - try both valid and invalid data to see the result

  10. When you are done, click the Save Data Changes button to commit your changes

  11. You now have a new table with a row of data

In the next exercise, we’ll look in more detail at a table, the objects within it, and how to manage them in pgAdmin.

Add a column

  1. Navigate to crime > street_crime and open the Columns node

  2. You will see that there is a wkb_geometry column - click on this and look at the Properties tab for it on the right - the data type is geometry, meaning that it is a spatial table and so can be used in GIS applications and analysed spatially

  3. Right-click on street_crime > View/Edit Data > All Rows to examine the data

  4. In QGIS, navigate to the same table under your PostgreSQL node in the Browser, and double-click to load it into QGIS

  5. Right-click > Open Attribute Table on the new layer in the Layers panel - you will see the same data as you saw in pgAdmin

  6. In pgAdmin, click on Columns > right-click > Create > Column

  7. Create a new column called officer - this will hold the name of the officer who dealt with the crime

  8. In the Definition tab, set:

    1. Data type: character varying

    2. Length/Precision: 200

  9. Click Save to add the column

  10. Open QGIS, right-click > Refresh on the connection and load the table again - you should see the new column

Other objects can be created in a similar way - note also that most objects can either be created from the right-click menu in pgAdmin, or by running SQL in the Query too.

Review Primary Key and Index

  1. Under street_crime, open the Constraints node and look at the Properties tab on the right

  2. Note that the key icon in the object tree indicates that this is a primary key, select the primary key in the object tree and the Columns field on the Properties tab indicates that ogc_fid is the key

  3. Compare the Properties with the SQL (in the SQL tab) used to create the primary key

  4. Under Indexes, look at the street_crime_sidx properties in the same way

  5. You see that the index properties include Access Method (gist, a spatial index) and the Column indexed (wkb_geometry)

  6. If you want to edit these values, click the Edit the Object button in the toolbar on the Properties tab to launch the edit dialog

4.2. Access Control

Login and Group Roles control access to database objects. This allows for the creation of Groups with certain privileges, and members of groups (i.e. individual users) who inherit those privileges.

The screenshot below shows that the postgis user is a member of the postgis group, and is able to create databases and inherit from its parent.

Access Control

  1. Right-click on street_crime and go to Properties

  2. In the Security tab, click in the field under the Privileges column to see the permissions granted to postgis_group

  3. From the Servers node, register a new Server, using the same settings as for the database you created at the beginning of the course but with user/password qgis/qgis

  4. Navigate to one of the tables and attempt to view the date

  5. As the qgis user does not have these permissions, access will be denied


  • No labels