PostgreSQL Essential Training

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

PostgreSQL is one of the world’s most popular open-source database platforms. This highly flexible and scalable relational database management system (RDBMS) supports everything from general purpose databases to machine learning applications and geospatial map servers. In this course, instructor Adam Wilbert helps you get up and running with PostgreSQL. Adam covers the essential first steps of setting up a PostgreSQL server and working with the graphical interface, pgAdmin. He then shows how to work with pgAdmin to create new database objects, populate them with data, and retrieve records from the database. If you're new to the platform (or relational databases in general), this course serves as an entry point and provides a solid foundation for further exploration in database administration.

Introduction

• Manage relational data with PostgreSQL
• Using the exercise files

1. Getting Started with PostgreSQL

• What is PostgreSQL?
• Download and install PostgreSQL
• Connect to the server with psql
• Browse the server with a graphical interface
• Create objects with pgAdmin
• Challenge: Create a table
• Solution: Create a table

2. An Introduction to Relational Databases

• The structure of a database table
• PostgreSQL native data types
• Join tables together with relationships

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. Retrieve 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
• Back up and restore a database
• Start and stop the server

Conclusion

• Additional resources