• Home
  • Introduction to PostgreSQL

Introduction to PostgreSQL

Length: 4 days (32 hours)

This is an introduction to relational databases in general, and PostgreSQL in particular. At the end of this course, participants should be familiar with the structure and usage of relational databases, basic SQL queries, how to insert and update data in the database, and the different ways in which information can be retrieved from the database.

Particular emphasis will be given on the use of constraints and data types to ensure data accuracy, the use of joins and subselects to combine information from different tables, and the use of indexes to retrieve data efficiently.

The course involves a large number of live coding demonstrations and hands-on exercises that participants will be expected to solve.  There will be one exercise for nearly every major section described below, and numerous exercises for some of the sections, especially when it comes to subselects and joins.

The course uses PostgreSQL 9.4 (i.e., the latest version), and assumes that every user will have access to a copy of PostgreSQL for exercises.

Audience: Newcomers to relational databases and/or PostgreSQL who want to learn how to create, modify, and retrieve data from PostgreSQL using standard tools.

  • Introduction to PostgreSQL
  • What is a relational database?
  • History of PostgreSQL
  • Pieces of a PostgreSQL installation: The “postgres” user, standard files, and directories
  • Clusters, databases, and schemas
  • Creating basic tables
  • Data types and associated functions
    • NULL
    • Boolean
    • Numeric (integer, float, double); octal, hex, and binary representations
    • Sequences and SERIAL
    • Primary keys
    • Text types (and functions)
    • Searching through text
    • Time and date
    • IP addresses
    • BLOBs, enums, arrays
  • Using the psql client
  • Tables
    • Defining tables
    • Primary keys
    • NOT NULL
    • Default values
    • DROP TABLE
  • Basic data manipulation
    • INSERT one row, multiple rows
    • UPDATE
    • DELETE
  • Basic SELECT queries
    • SELECT * — why not?
    • Column aliases
    • ORDER BY
    • Subselects (EXISTS, IN/NOT IN)
    • Aggregate functions
    • GROUP BY
  • Joins
    • What is a join?
    • Regular joins
    • Cartesian products
    • Inner joins
    • Outer joins
  • Views
    • Creating views
    • Using views
    • Materialized views
  • Temp tables
  • Importing and exporting data
  • SELECT INTO
  • SELECT INTO OUTFILE
    Using psql to import data
  • Importing CSV or tab-delimited data
  • CSV/tab-delimited output
  • HTML output
  • XML input and output
  • pg_dump
>