Using Postico to modify a PostgreSQL database - axismaps/bcworkshop-collect GitHub Wiki

What's a database?

There are a few things that make a database different from a regular spreadsheet:

1. A database is a collection of tables, not just a single table.
2. Tables in a database have structure, or rules and constraints about the data they contain (e.g., Primary Key below).
3. Databases allow us to create complex relationships among the data. By using queries, we can combine and filter the data in helpful ways.

In other ways, tables in a database are similar to spreadsheets:

1. Tables in a database store data about one type of thing (e.g., neighborhoods and their names, sizes, populations).
2. Their rows (aka records) are used to represent those individual "things" and their columns are used describe those "things".

PRIMARY KEY: One kind of database constraint is called a Primary Key, which is used to uniquely identify records in a table. It ensures data integrity within a column by not allowing duplicate values. A Primary Key column is sometimes used to join tables of data together when linked with another kind of constraint called a foreign key.

Connecting to a database

To connect to the online database, open Postico and enter the connection parameters in a new favorites window. Then click Connect:

Once you're connected to the database, Postico will show you a view of all of the tables. Double-click a table to view its contents.

Modifying records

Modifying records is a simple process of directly editing a cell and typing a change or selecting a row (or rows) and making changes in Postico's sidebar:

Basic Queries

###SQL SQL is a standard language for accessing and manipulating databases. There's a great introduction here. For example, with SQL you can update or delete records in the database, as well as perform more sophisticated operations such as to combine and create new tables from your data. Learn more about PostgreSQL, the open-source database, here.

Try a few simple queries on the bcworkshop database:

SELECT * FROM neighborhoods

Returns a table of all of the columns from the neighborhoods table.

SELECT name, description FROM neighborhoods

Returns a table with just the name and description columns from the neighborhoods table.

SELECT name, description FROM neighborhoods WHERE id = 209

Returns a table with the name and description columns from the neighborhoods table for the single record with an id of 209.

SELECT neighborhoods.id, name, link FROM neighborhoods INNER JOIN resources ON neighborhoods.id = neighborhood

Returns a table with the id and name columns from the neighborhoods table and the link column from the resources table.

###PostGIS PostGIS is open-source software that adds spatial functions to PostgreSQL. In other words, it allows Postgres to be used as a spatial database. Spatial queries can be written to do GIS types of analyses using the geometry of features. A good discussion on spatial databases and functions can be found here.

See the Template System wiki for a run-down of the basic queries used for bcworkshop.