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 4 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 what can be done with it in pgAdmin.

  • Columns 

  • Constraints include requirements for records in a table - for example the primary key, or the geometry type the data must represent

  • Indexes are included to speed up search and retrieval of data

  • Triggers define actions to be taken when an event happens - for example when a new row is added to a table

  • Functions are equivalent to stored procedures - they are canned SQL which performs a task

  • Sequences implement auto-numbering

  • Views are virtual tables which are the result of SQL applied to one or more real tables, and ordinary views can not be edited

For a database cluster:

  • Group and Login Roles control access and rights to schemas and tables:

    • Login Roles represent individual users, setting rights on database objects (for example CREATE, UPDATE, VIEW, DELETE)

    • Group Roles can also be granted rights, and can also contain users, so many users can easily be allocated the same rights

Tables

Datatypes

I

Goal of Exercise

  1. Do this

  2. Do something else - this is what you get


  • No labels