PostgreSQL
psql, schemas, roles, indexes and backups.
Level 201 · 18 lessons · 15 practice labs
Lessons
Getting started
- Installing PostgreSQL and connecting with psql: You learn to install PostgreSQL in your lab, connect to the database with the psql tool, and use the basic commands.
- PostgreSQL schemas, psql meta-commands and the system catalog: You will group objects into schemas and explore a database with psql’s \d commands and the pg_catalog views.
- PostgreSQL databases, data types and SERIAL: You learn to create a PostgreSQL database, choose suitable data types for columns, and use an auto-increasing number (SERIAL).
- PostgreSQL constraints and foreign keys: You learn to keep invalid data out of a PostgreSQL table with constraints and to link tables consistently with foreign keys.
- Short task: set up a shop’s schema
Queries
- Insert, query, update, delete in PostgreSQL: You learn to insert, query, update and delete data in PostgreSQL without accidentally changing every row.
- PostgreSQL relationships and joins: You will understand how tables relate in PostgreSQL and learn to pull data from several tables in one query using joins.
- PostgreSQL: aggregates, GROUP BY and HAVING: You will group rows, calculate counts, sums and averages, and filter the group results with HAVING.
- PostgreSQL subqueries and set operations: You will nest queries and combine or compare the results of two queries with UNION and INTERSECT.
- Importing and exporting data with COPY
- PostgreSQL indexes and the query plan: You learn how PostgreSQL indexes speed up queries and how to read a query plan to see why a query is slow.
- PostgreSQL transactions: You will understand PostgreSQL transactions, which make a group of related changes either all succeed together or not happen at all.
- Short task: a report from order data
Administration and logic
- PostgreSQL views, functions and triggers: You learn to save frequent queries as views in PostgreSQL and what functions and triggers are used for inside the database.
- PostgreSQL roles and permissions: You learn to create PostgreSQL roles, give each user only the permissions they need, and take back any that are not needed.
- postgresql.conf and the logs
- PostgreSQL backup and restore: You learn to back up a PostgreSQL database and restore it to prove that the backup really works.
- Capstone: an orders database in PostgreSQL: For a fictional company you build a small orders database in PostgreSQL with tables, constraints, roles and a backup.
Practice labs
- Set up a shop schema: Create customer, product and order tables with identity columns in a separate schema.
- Add the missing foreign keys: Find orphan orders, clean them up and then add validated foreign keys.
- Add a constraint without an outage: Add a CHECK constraint to a big table as NOT VALID first, validate it separately and compare lock times.
- Import a CSV with COPY: Import Kuzey Eczanesi's medicine list with \copy and check the row count.
- Export a query result: Export the monthly order report as a CSV with a header and semicolon separators.
- Sift bad rows through a staging table: Load a file with bad dates and prices into a text staging table, move the clean rows and report the bad ones.
- Summarise sales per category: Calculate order count, revenue and average basket per category in one query.
- Compare two months' customers with EXCEPT: Use EXCEPT to find customers who bought last month but not this month.
- Write a one-query report for management: Build a management report giving new customers, returning customers, revenue and return rate in one query.
- See index use in a query plan: Add an index and show a sequential scan turn into an index scan in EXPLAIN.
- Update order and stock together: Wrap inserting an order and reducing stock in one transaction and show both roll back on error.
- Weed out unused indexes: Use statistics views to find never-used indexes, calculate their size and write a removal plan.
- Turn a complex query into a view: Define a frequently used order summary query as a view and grant the app access to it.
- Maintain updated_at with a trigger: Write a trigger function that sets the updated_at column automatically on every update.
- Safe updates through a view: Set up a view WITH CHECK OPTION so users can change only their own branch's rows.
About this course
psql, schemas, roles, indexes and backups.
What you will learn
- Getting started
- Queries
- Administration and logic
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 201; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.