Databases, Schemas, and Tables

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Databases, Schemas, and Tables
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

A server instance contains databases; a database contains schemas; and a schema contains tables and other objects. Schemas are useful naming and permission boundaries. Instead of placing every project object in the default `public` schema, this course uses an `app` schema.

Connect it to a real scenario

Create `app.users` with an identity primary key, email, display name, active flag, and creation time. Schema-qualified names reduce accidental reliance on `search_path` and make the referenced object explicit.

Cluster → database → schema → table hierarchy
Keep project objects in the `app` schema
Identify each row with a primary key

Try the working example

sql

CREATE SCHEMA IF NOT EXISTS app;

CREATE TABLE app.users (
  user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  display_name text NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

\d app.users

You should see

`psql` describes the new `app.users` table, its columns, and constraints.

5-minute try-it

Create `app.categories` yourself with an identity key, unique name, and creation time.

One important caution

Quoted MixedCase identifiers require quotes in every query. Prefer consistent lowercase snake_case names.

PostgreSQL — Data Definition — PostgreSQL Global Development Group

Easy traps

Quoted MixedCase identifiers require quotes in every query. Prefer consistent lowercase snake_case names.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Create `app.categories` yourself with an identity key, unique name, and creation time.

You'll know it worked when: `psql` describes the new `app.users` table, its columns, and constraints.