PostgreSQL Essential Training

Learn how to set up and work with one of the world’s most popular open-source database platforms, PostgreSQL.

Introduction

• Manage relational data with PostgreSQL

1. Getting Started with PostgreSQL

• What is PostgreSQL?
• Download and install PostgreSQL
• Install PostgreSQL with Docker
• Connect to the server with psql
• Browse the server with a graphical interface
• Connect pgAdmin to Docker
• Tour the pgAdmin interface
• Use pgAdmin’s toolset
• Create objects with pgAdmin's dialog workflows

2. An Introduction to Relational Databases

• The structure of a database table
• PostgreSQL native data types
• Join tables together with relationships
• Create a table and insert rows of data
• Challenge: Create a table
• Solution: Create a table

3. Building a Database

• Organize tables with schemas
• Create a table with pgAdmin
• Link primary and foreign keys
• Enforce referential integrity on related records
• Challenge: Execute a command on the Postgres server
• Solution: Execute a command on the Postgres server

4. Retrieving Information with Queries

• Import data from a CSV
• Retrieve information with a SELECT query
• Helpful pgAdmin Query Tool interface elements
• Join tables for additional information
• Save a query as a database view
• Challenge: Import data and write queries
• Solution: Import data and write queries

5. Managing Data

• Add indexes to a table
• Automatically fill in default values
• Constrain acceptable input values
• Challenge: Add data constraints
• Solution: Add data constraints

6. Database Administration in PostgreSQL

• User accounts and roles
• Grant privileges to a role
• Backup and restore a database
• Start and stop the server

Conclusion

• Next steps