Databases, Schemas & Tables

Databases, Schemas & Tables

PostgreSQL organises objects in a three-level hierarchy: server → database → schema → table. A single Postgres server can host many databases; each database is isolated and has its own set of schemas. A schema is a namespace that groups related tables, views, functions, and types.

-- Create a new database
CREATE DATABASE cpcode_app;

-- Connect to it (psql meta-command)
-- \c cpcode_app

-- Create a schema for the public-facing API layer
CREATE SCHEMA api;

-- Create a schema for internal admin tables
CREATE SCHEMA admin;

By default every database has a public schema. The search_path setting controls which schemas Postgres looks in when you reference an unqualified table name. At CPCODELAB we always set search_path to public in the connection string so behaviour is explicit.

-- Show the current search path
SHOW search_path;

-- Set a custom search path for the session
SET search_path TO api, public;

-- Set permanently for a role
ALTER ROLE myuser SET search_path TO public;

Tables are created with CREATE TABLE. Postgres enforces column types strictly — inserting a string into an integer column raises an error rather than silently coercing the value.