Skip to content
Ameen Abozaid
Englishالعربية
BEG-051

PostgreSQL basics

  • 6 Lessons
  • 64 min of reading
  • 3 h practice (estimate)
Start module
On this page 8 topics

Overview

Topics

  • Installation
  • pgAdmin/DBeaver
  • schemas
  • constraints
  • CRUD
  • transactions introduction

Learn first

64 min of reading · 3 h of practice (estimate)

Learning outcomes

  1. Install PostgreSQL and connect with psql and a GUI client (pgAdmin or DBeaver).
  2. Create a database, schema, and user.
  3. Apply NOT NULL, UNIQUE, CHECK, and foreign-key constraints and observe rejected data.
  4. Wrap several statements in a transaction and roll it back.

Lessons

  1. 01Installing PostgreSQL11 min read
  2. 02psql and a GUI client11 min read
  3. 03Databases, schemas, and users11 min read
  4. 04Constraints that protect data10 min read
  5. 05First look at transactions10 min read
  6. 06Demo: creating the Library database with constraints11 min read

Lab and exercises

Lab

BEG-051-LAB1 · Library database live

Outcomes: 1–4 · Time: ~3 h Create the Library schema from BEG-049 as a SQL script with constraints and seed data. Try to insert invalid rows and record the errors. Demonstrate a rollback. Acceptance: script re-runnable from scratch.

Exercises (assessment: practical check of the script)

IDOutcomeTask
BEG-051-EX011Install PostgreSQL, connect with psql, and run SELECT version();.
BEG-051-EX021Connect with pgAdmin or DBeaver and browse the tables of a database.
BEG-051-EX032Create a user library_app, a database library owned by it, and connect as that user.
BEG-051-EX043Add NOT NULL, UNIQUE, and CHECK constraints to books; write one failing insert for each.
BEG-051-EX053Add a foreign key from loans to members and show the error for a missing member.
BEG-051-EX064Run an UPDATE inside BEGIN, check the result, then ROLLBACK and check again.
BEG-051-EX072Explain why an application should not connect as postgres.
Show solutions
  • EX01 psql -U postgres, then SELECT version();.
  • EX02 Register a server on localhost:5432, open Schemas > public > Tables.
  • EX03 CREATE USER library_app WITH PASSWORD '...'; CREATE DATABASE library OWNER library_app; then psql -U library_app -d library.
  • EX04 Null title -> NOT NULL error; repeated ISBN -> unique violation; copies = -1 -> check violation.
  • EX05 member_id INTEGER NOT NULL REFERENCES members(id); inserting member_id = 999 fails with a foreign key violation.
  • EX06 Before rollback the changed value shows; after rollback it is back to the original.
  • EX07 The superuser can change or drop everything; least privilege limits damage from bugs or leaks.

Quiz

Outcomes: O1 install and connect · O2 database, schema, user · O3 constraints · O4 transactions.

A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.

  1. 1The command-line client for PostgreSQL is:O1
  2. 2The default PostgreSQL port is:O1
  3. 3In psql, \dt lists:O1
  4. 4An application should connect as:O2
  5. 5The default schema name is:O2
  6. 6To forbid duplicate ISBNs:O3
  7. 7To forbid negative copies:O3
  8. 8An insert with a non-existent member id is rejected by:O3
  9. 9ROLLBACK will:O4
  10. 10Constraints in the database matter because:O4

Practical part

Practical check of the re-runnable schema script with constraints, recorded errors, and a demonstrated rollback (lab and EX01–EX07). Pass: script runs from scratch, at least 8 of 10 quiz questions.

Common mistakes

Working as the postgres superuser; constraints only in application code.

Assessment

Practical check of the script.

Career relevance

Primary database for the whole curriculum.

Type to search levels, phases, modules and lessons.