Creating Databases, Schemas, and Tables

PostgreSQL organises data in a three-level hierarchy: a server cluster contains multiple databases, each database contains schemas (namespaces), and each schema contains tables, views, functions, and other objects. The default schema is called public. Schemas provide a way to group related objects and control access without creating entirely separate databases. CREATE TABLE defines the structure of a table with column names, data types, and constraints. Constraints can be defined inline with a column (column-level) or after all columns (table-level), which is required for multi-column constraints. PostgreSQL supports a rich set of constraints: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY, and EXCLUDE.

Example

-- Create a database
CREATE DATABASE myappdb
  WITH ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE   = 'en_US.UTF-8';

-- Create a schema
CREATE SCHEMA IF NOT EXISTS shop;

-- Create a table with various constraints
CREATE TABLE shop.products (
  id          SERIAL PRIMARY KEY,
  sku         VARCHAR(50)    NOT NULL UNIQUE,
  name        VARCHAR(255)   NOT NULL,
  description TEXT,
  price       NUMERIC(10,2)  NOT NULL CHECK (price >= 0),
  stock       INTEGER        NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category_id INTEGER        REFERENCES shop.categories(id) ON DELETE SET NULL,
  is_active   BOOLEAN        NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- Multi-column unique constraint (table-level)
ALTER TABLE shop.products
  ADD CONSTRAINT uq_products_name_category UNIQUE (name, category_id);

-- Auto-update updated_at via a trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_updated_at
  BEFORE UPDATE ON shop.products
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned