Databases, Schemas, and Types

Introduction

PostgreSQL organizes data in databases and schemas before you reach tables and columns. This chapter creates your first database and schema, sets search_path, builds tables with modern identity columns, and picks the right PostgreSQL data types—including TEXT, TIMESTAMPTZ, UUID, arrays, and a preview of JSON vs JSONB. These foundations match what every later SQL chapter assumes.

Prerequisites

• Installation and psql
• Connected to PostgreSQL as a user who can create databases (e.g. postgres in Docker)
• Familiarity with MySQL database basics helps if you are migrating

Databases vs Schemas

In MySQL, database and schema are often used interchangeably. In PostgreSQL they are different layers:

Cluster (one server instance)
└── Database: shop
    ├── Schema: public      ← default namespace
    ├── Schema: billing
    └── Schema: analytics
        └── Table: orders   ← shop.analytics.orders when fully qualified

Code explanation:

• One PostgreSQL cluster hosts many databases—users, extensions, and backups are often scoped per database
• Schemas group tables without spinning up another server—common for staging vs app modules or multi-tenant patterns (advanced)

List and Create Databases

In psql, list databases:

-- Meta-command in psql (not standard SQL)
\l

Create a practice database:

CREATE DATABASE shop_dev
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0;

Connect to it:

-- psql meta-command
\c shop_dev

Or from the shell:

psql "postgresql://postgres:learnpass@127.0.0.1:5432/shop_dev"

Confirm current database:

SELECT current_database();

Code explanation:

• ENCODING 'UTF8' is the modern default—stores emoji and all Unicode without MySQL's historic utf8 vs utf8mb4 confusion
• LC_COLLATE / LC_CTYPE control sort order and character classification—set at create time; changing them later requires dump/restore
• TEMPLATE template0 gives a clean empty database ( template1 is the default but can carry objects )

Idempotent create:

-- PostgreSQL 15+ supports IF NOT EXISTS on CREATE DATABASE
CREATE DATABASE shop_dev;
-- If it already exists, you get an error unless you use a client-side guard or drop first

Drop Database

DROP DATABASE removes the database and all its schemas and tables:

-- Disconnect other sessions first, or use FORCE (PostgreSQL 13+)
DROP DATABASE IF EXISTS shop_old;

Compare with MySQL database management—MySQL uses CHARACTER SET utf8mb4; PostgreSQL uses ENCODING and locale at database creation.

Schemas and search_path

Create a schema for a billing module:

CREATE SCHEMA IF NOT EXISTS billing;

List schemas:

-- psql
\dn
 
-- SQL
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;

Set search path for the session—PostgreSQL looks here for unqualified names:

SHOW search_path;
-- Typical default: "$user", public
 
SET search_path TO billing, public;

Create a table in a specific schema without changing search_path:

CREATE TABLE billing.invoices (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount NUMERIC(12, 2) NOT NULL
);

Code explanation:

• search_path is like a list of "folders" checked left to right—first match wins
• public is where CREATE TABLE users goes by default
• Fully qualified names (billing.invoices) always work regardless of search_path

Multi-Schema Organization (Concept)

Keep learning schemas in public until Roles and Permissions.

Your First Table

Blog-style users table with modern identity and comments:

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL,
  display_name TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT uk_users_email UNIQUE (email)
);
 
COMMENT ON TABLE users IS 'Application user accounts';
COMMENT ON COLUMN users.email IS 'Login email; unique case-sensitively unless citext extension is used';

Verify:

-- psql
\dt
\d users
 
-- SQL
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'users'
ORDER BY ordinal_position;

Code explanation:

• GENERATED ALWAYS AS IDENTITY is the SQL-standard replacement for SERIAL—preferred for new tables
• TEXT has no length penalty vs VARCHAR(n) in PostgreSQL—use TEXT unless you need a hard max length
• TIMESTAMPTZ stores UTC internally and displays in the session timezone—prefer it over TIMESTAMP without time zone
• UNIQUE constraint enforces one row per email—same idea as MySQL UNIQUE KEY

Compare table creation with MySQL creating tables—no ENGINE=InnoDB or AUTO_INCREMENT syntax.

SERIAL vs IDENTITY vs Sequences

Older PostgreSQL code uses SERIAL:

CREATE TABLE legacy_notes (
  id SERIAL PRIMARY KEY,
  body TEXT NOT NULL
);

SERIAL is shorthand: it creates an integer column plus a SEQUENCE and attaches a default nextval(...).

Explicit sequence (when you need shared counters or custom steps):

CREATE SEQUENCE order_number_seq START 1000 INCREMENT 1;
 
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_number BIGINT NOT NULL DEFAULT nextval('order_number_seq'),
  user_id BIGINT NOT NULL,
  total NUMERIC(10, 2) NOT NULL
);

DROP and TRUNCATE

Remove a table and its data permanently:

DROP TABLE IF EXISTS legacy_notes;

Empty a table but keep structure (fast reset; triggers and identity rules still apply):

TRUNCATE TABLE users RESTART IDENTITY CASCADE;

Code explanation:

• TRUNCATE is faster than DELETE without a WHERE clause but cannot be rolled back easily in some contexts—use inside transactions when unsure
• RESTART IDENTITY resets IDENTITY / sequence counters
• CASCADE also truncates tables that foreign-key reference this table—use carefully; foreign keys come in Constraints and Keys

PostgreSQL Data Types

Numeric

-- Example column definitions
price NUMERIC(10, 2) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0

Strings

PostgreSQL does not require VARCHAR(255) for indexing—unlike some MySQL habits.

Date and Time

SELECT NOW();                    -- timestamptz in session TZ
SELECT CURRENT_TIMESTAMP;      -- same family
SET TIME ZONE 'UTC';           -- affects display, not stored UTC for timestamptz

Boolean

Native BOOLEAN—no MySQL TINYINT(1) workaround:

is_active BOOLEAN NOT NULL DEFAULT TRUE

UUID

Store opaque IDs—common in APIs:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
 
CREATE TABLE api_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id BIGINT NOT NULL,
  label TEXT NOT NULL
);

gen_random_uuid() is built into PostgreSQL 13+; older versions rely on pgcrypto.

Arrays

PostgreSQL columns can hold arrays—handy for tags and small lists:

CREATE TABLE posts (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT[] NOT NULL DEFAULT '{}'
);
 
-- Example query (insert covered in next chapter)
SELECT id, title FROM posts WHERE 'postgres' = ANY (tags);

Code explanation:

• TEXT[] is a variable-length list of strings
• ANY (tags) checks membership—GIN indexes can speed this up later

JSON vs JSONB (Preview)

CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY,
  settings JSONB NOT NULL DEFAULT '{}'::jsonb
);

Deep indexing and operators live in JSONB and Advanced Types. For document-first modeling, see MongoDB document basics.

Posts Table Exercise

Build on users—foreign key details expand in chapter 9:

CREATE TABLE posts (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  published_at TIMESTAMPTZ,
  tags TEXT[] NOT NULL DEFAULT '{}',
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb
);
 
CREATE INDEX idx_posts_user_id ON posts (user_id);

Check objects:

\dt
\d posts

Keep shop_dev (or hello_demo) with users and posts for Insert and COPY.

PostgreSQL vs MySQL Type Quick Reference

FAQ

Schema vs database—which do I pick?

Use separate databases for hard isolation (different apps, restore units). Use schemas inside one database to organize modules or teams without extra connection overhead.

Why not use VARCHAR(255) everywhere?

PostgreSQL treats TEXT and VARCHAR similarly for performance. Use VARCHAR(n) only when the application or standard requires a max length.

Can I change LC_COLLATE after creation?

Not in place. Create a new database with the desired locale and pg_dump / restore, or use C / POSIX collation for locale-independent sorts if you plan migrations.

SERIAL or IDENTITY for new projects?

Prefer GENERATED ALWAYS AS IDENTITY. It is standard SQL and avoids subtle sequence-ownership surprises when cloning tables.

Where did my table go after CREATE TABLE?

Check search_path and \dt *.*. The table may live in another schema. SELECT * FROM pg_tables WHERE tablename = 'users'; shows the schema name.

JSON or JSONB?

Default to JSONB unless you must preserve exact key order and whitespace from input. Most apps query and merge JSON—JSONB is built for that.

Is UTF8 enough for emoji?

Yes—PostgreSQL UTF8 covers full Unicode. You do not need a separate utf8mb4 alias like MySQL.