3.2Data Definition and Database Objects (Data Types, Constraints, Indexes, Views, etc.)

To query data with SQL, you first need to design the container that holds the data correctly. Beyond the basic table unit, PostgreSQL offers a variety of database objects—views, triggers, sequences, and more—each serving a distinct role: preserving integrity, preventing duplication, or reusing common queries. This section surveys everything from basic design to more operational objects.

3.2.1Table definition, data types, and constraints

• CREATE TABLE defines a new table (CREATE TABLE t (id INTEGER PRIMARY KEY, name VARCHAR(50))). ALTER TABLE adds columns, changes types, adds constraints, etc. (ALTER TABLE t ADD COLUMN email TEXT). DROP TABLE removes the entire table (a destructive operation removing both data and structure).
• Key data types: integers INTEGER (4 bytes) and BIGINT (8 bytes, larger range); decimals NUMERIC (arbitrary precision, suited to monetary calculations); strings VARCHAR (variable-length, optionally capped) and TEXT (unlimited length); booleans BOOLEAN; date/time DATE (date only) and TIMESTAMP (date and time; timezone-aware form is TIMESTAMP WITH TIME ZONE).
• JSON stores JSON data as plain text (preserving the input's exact appearance). JSONB stores it parsed, in binary form (duplicate keys removed, key order not preserved, but faster to search and index). JSONB is generally the recommended choice in practice.
• Constraints: PRIMARY KEY (a primary key combining uniqueness with NOT NULL) / FOREIGN KEY (references another table's primary key, enforcing referential integrity) / UNIQUE (forbids duplicates but allows multiple NULLs) / CHECK (an arbitrary condition such as CHECK (age >= 0)) / NOT NULL (forbids NULL values).

3.2.2Indexes, views, and other database objects

• An index is an auxiliary structure that speeds up lookups (CREATE INDEX idx_name ON t (col)), trading off against write-time update cost. A view saves a query result as a virtual table (CREATE VIEW v AS SELECT ...; it holds no actual data and re-executes on each reference). A materialized view stores the result physically, refreshed via REFRESH MATERIALIZED VIEW (fast to read, but freshness depends on when it was last refreshed).
• A trigger automatically runs a function when a specific event (INSERT/UPDATE/DELETE) occurs (CREATE TRIGGER). A sequence is an object that generates auto-incrementing values (CREATE SEQUENCE; the underlying mechanism behind the SERIAL auto-numbering primary key type). A schema is a namespace grouping tables and other objects (qualified as schema.table; the default is public).
• A tablespace (TABLESPACE) designates the physical storage directory for tables and indexes (CREATE TABLESPACE, used to spread data across disks). Partitioning divides one logical table into multiple physical tables (by range, list, etc.), aiding management and query efficiency for large volumes of data.
• Functions/procedures: logic implemented inside the database using a procedural language such as PL/pgSQL (CREATE FUNCTION), also used as the body a trigger executes. Streaming/logical replication: at a basic conceptual level only, understand that changes are transferred via WAL to another server to maintain a copy (construction, monitoring, and failure recovery are Gold-level topics).

Consider designing a new e-commerce database and walk through how each object fits together. Creating CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price NUMERIC(10,2) CHECK (price >= 0), attributes JSONB) auto-numbers the id column from an internally generated sequence (products_id_seq), applies a CHECK constraint requiring price >= 0, and stores flexible extra product info (color, size, etc.) in a JSONB column. The orders table adds product_id INTEGER REFERENCES products(id) as a FOREIGN KEY, preventing orders against nonexistent product IDs. Since orders.product_id is searched frequently, CREATE INDEX idx_orders_product ON orders (product_id) speeds up join lookups—balanced against the cost of updating the index on every write. A heavy aggregation like "monthly sales ranking by product" is expensive to recompute every time, so the standard design is a materialized view (CREATE MATERIALIZED VIEW monthly_sales AS SELECT ...), refreshed nightly via REFRESH MATERIALIZED VIEW monthly_sales (an ordinary view re-executes against the base tables on every SELECT, making it unsuitable for heavy aggregation). To automatically log a notification whenever stock hits zero, combine a trigger firing on UPDATE to products with a PL/pgSQL function holding the actual logic. As orders grows huge month over month, partitioning into per-month physical tables makes searching and deleting old data more efficient. For replication, it is enough at this level to understand the concept—changes on the primary are streamed via WAL to maintain a read-only replica; actual construction procedures and parameter tuning are outside this exam's scope.

3.2.3Section summary

• Manage tables with CREATE/ALTER/DROP TABLE. PRIMARY KEY = unique + NOT NULL; UNIQUE allows multiple NULLs; FOREIGN KEY = referential integrity; CHECK = arbitrary condition
• JSONB = parsed binary, fast to search; JSON preserves text. View = virtual, re-executes each time; materialized view = stored physically, needs REFRESH
• Trigger = event-driven function execution; sequence = auto-numbering; partitioning = physical splitting. Replication is basic concepts only at the Silver level