🚧 Under construction — VoTechWorks is being built. A free tech program — complimentary, as our commitment to get America working.
voTechWorksGrade 13 · a vocational technology learning center, by urbanicity
newSchoolWorks.com › VoTechWorks › Technology & Design › Data Management / Database Administration › PostgreSQL Administration › Lesson 3
TEC-DATA86-03 · Lesson 3 · 12 clock hours · lab

Database Objects: Databases, Schemas, Tables, Sequences, and Views

Objective. Students create a multi-schema database with tables, sequences, views, and constraints using both psql DDL commands and the pgAdmin GUI.

⚠ Pending professional review

This lesson, its worked examples and its practice questions has not yet been verified by an industry-certified professional or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.

VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.

Database Objects in PostgreSQL

A PostgreSQL server can host many databases. Each database is an isolated container: objects inside one database cannot directly reference objects in another. When you connect with psql or pgAdmin, you connect to exactly one database at a time. For this lab you will create `inventory_db` and work entirely within it.

Schemas

Inside a database, schemas are namespaces that organize objects. Every database starts with the `public` schema. You can add schemas to separate concerns — for example, `warehouse` for stock-related tables and `sales` for order-related tables. Object names are qualified with the schema name: `warehouse.product` refers to the `product` table in the `warehouse` schema. The search path (`search_path`) controls which schemas PostgreSQL checks when an unqualified name is used.

```sql

CREATE SCHEMA warehouse;

CREATE SCHEMA sales;

```

Tables, Primary Keys, and Constraints

Tables store data in rows and columns. When you define a table you also declare constraints that enforce data integrity:

```sql

CREATE TABLE warehouse.product (

product_id INT PRIMARY KEY,

sku VARCHAR(50) NOT NULL UNIQUE,

unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0)

);

```

Foreign keys that span schemas work the same way — just use the fully qualified table name in the REFERENCES clause.

Sequences

A sequence is a database object that generates a series of integers. You can create one explicitly:

```sql

CREATE SEQUENCE warehouse.product_id_seq START 1 INCREMENT 1;

```

Associating it with a column using `OWNED BY` means the sequence is dropped automatically when the column or table is dropped — keeping the database tidy.

PostgreSQL offers three shorthand approaches for auto-incrementing integer columns:

| Approach | Introduced | Notes |

|---|---|---|

| `SERIAL` / `BIGSERIAL` | Early PostgreSQL | Creates a sequence implicitly; NOT SQL-standard |

| `GENERATED ALWAYS AS IDENTITY` | PostgreSQL 10 | SQL-standard; preferred for new designs |

`BIGSERIAL` uses an 8-byte integer and is appropriate when a table may exceed roughly two billion rows. `GENERATED ALWAYS AS IDENTITY` is the current best practice because it is part of the SQL standard and gives the database tighter ownership of the sequence.

Views

A view is a saved SELECT statement stored as a named object. It does not duplicate data; each time you query the view, PostgreSQL runs the underlying SELECT. Views simplify complex queries, control what columns a user can see, and can join tables across schemas.

```sql

CREATE VIEW sales.inventory_summary AS

SELECT s.order_id, w.sku, s.quantity

FROM sales.order_line s

JOIN warehouse.product w ON s.product_id = w.product_id;

```

Generated Columns

PostgreSQL 12 introduced generated columns — columns whose value is always computed from other columns in the same row. The keyword `GENERATED ALWAYS AS (expression) STORED` writes the computed value to disk.

```sql

ALTER TABLE warehouse.product

ADD COLUMN price_with_tax NUMERIC(10,2)

GENERATED ALWAYS AS (unit_price * 1.08) STORED;

```

You cannot insert or update a generated column directly; PostgreSQL manages it.

Exporting DDL with pg_dump

The `pg_dump --schema-only` flag extracts the Data Definition Language (DDL) for all objects without exporting any row data. This is useful for version control, peer review, and migration.

```bash

pg_dump --schema-only -d inventory_db -f inventory_db_schema.sql

```

Review the output file to confirm every table, constraint, sequence, and view appears as expected.

Working Safely with the Lab VM

Although this is a software environment, safe data-management habits begin here:

pgAdmin 4 Tips

pgAdmin 4 displays the object tree in the left panel. Expand Databases → inventory_db → Schemas to browse tables, views, sequences, and constraints visually. Right-click any object for scripted DDL. Use Tools → Query Tool to run SQL and see results side by side. Annotate your screenshots with the built-in snipping tool or any image editor before pasting them into the lab report template.

Key terms10

SchemaA named namespace inside a PostgreSQL database that groups related objects such as tables, views, and sequences, allowing the same object name to exist in different schemas without conflict.
Primary KeyA column or combination of columns that uniquely identifies every row in a table; PostgreSQL automatically enforces NOT NULL and UNIQUE on the primary key column(s).
Foreign KeyA constraint that requires values in one table's column to match values in the primary key column of a referenced table, enforcing referential integrity.
SequenceA database object that produces a series of unique integer values, commonly used to generate surrogate primary key values.
GENERATED ALWAYS AS IDENTITYA SQL-standard syntax introduced in PostgreSQL 10 for auto-incrementing columns; preferred over the older SERIAL shorthand for new table designs.
ViewA stored SELECT statement saved as a named database object; querying the view re-executes the SELECT, so the results always reflect current table data.
Generated ColumnA table column whose value is always computed automatically from an expression involving other columns in the same row, introduced in PostgreSQL 12 with the STORED keyword.
DDL (Data Definition Language)The subset of SQL statements — including CREATE, ALTER, and DROP — that define or modify the structure of database objects rather than the data they contain.
CHECK ConstraintA column or table constraint that evaluates a Boolean expression for each row; any row that makes the expression false (not unknown) is rejected by PostgreSQL.
pg_dumpA PostgreSQL utility that exports a database's structure, data, or both to a script or archive file; the --schema-only flag exports only DDL with no row data.
In class: Using psql, create a database named inventory_db, add two schemas (warehouse and sales), and build at least six tables with primary keys, foreign keys, NOT NULL constraints, and a CHECK constraint; export the DDL with pg_dump --schema-only and review it. · Use pgAdmin 4 to inspect the object tree for inventory_db, modify a table by adding a generated column, create a view joining two tables across schemas, and take annotated screenshots of each step for a lab report. · Create a sequence manually, associate it with a column using OWNED BY, then demonstrate the difference between SERIAL, BIGSERIAL, and GENERATED ALWAYS AS IDENTITY to a partner using a prepared script, explaining which PostgreSQL versions introduced each approach.
Materials: PostgreSQL 16 server VM, pgAdmin 4, psql, PostgreSQL Documentation Chapter 5 (DDL), lab report template, sample dataset (CSV files for import)
Check: Instructor runs a provided verification script that queries pg_tables, pg_views, and information_schema.columns to confirm correct object creation; pass = all objects present with correct constraints AND lab report submitted.

Practice check8

Answer each question, then check it. Nothing is recorded; this is your own practice.

PostgreSQL Administration – Schema Management
1. You run `CREATE TABLE warehouse.product (...)` in `inventory_db`. A colleague connected to `inventory_db` runs `SELECT * FROM product;` without a schema prefix and gets a 'relation does not exist' error. What is the most likely cause?
PostgreSQL Administration – DDL and Object Management
2. Which PostgreSQL version first introduced the `GENERATED ALWAYS AS IDENTITY` syntax for auto-incrementing columns?
PostgreSQL Administration – DDL and Object Management
3. You want a column `price_with_tax` to always equal `unit_price * 1.08` and be written to disk automatically. Which DDL clause achieves this in PostgreSQL 12 or later?
PostgreSQL Administration – DDL and Object Management
4. A developer uses `BIGSERIAL` instead of `SERIAL` for a new table's primary key column. What is the practical difference?
PostgreSQL Administration – Sequences and Identity
5. After creating a sequence with `CREATE SEQUENCE warehouse.product_id_seq`, you use `OWNED BY warehouse.product.product_id`. What behavior does `OWNED BY` enforce?
PostgreSQL Administration – Views
6. You create a view `sales.inventory_summary` that joins `sales.order_line` and `warehouse.product`. A user queries the view an hour after new rows were inserted into `warehouse.product`. Which rows will the view return?
PostgreSQL Administration – Constraints and DDL
7. The instructor's verification script queries `information_schema.columns` and finds that a `CHECK` constraint requiring `unit_price >= 0` is missing. Which of the following commands correctly adds it to an existing table?
PostgreSQL Administration – Backup and DDL Export
8. You need to extract only the DDL (no row data) for `inventory_db` into a file called `schema_only.sql`. Which command is correct?
Record this lesson on my transcript. Sign in with your key and enrollment email to record lessons (enroll free if you have no key). This page stays open to read and practise.
← Previous lessonProgram planNext lesson →