Schemas for namespace organization in PostgreSQL

Introduction

Schemas help keep database objects organized by grouping them into separate spaces. This avoids name conflicts and makes managing data easier.

You want to separate tables for different departments in a company.

You need to keep test data separate from real data in the same database.

You want to organize database objects by project or feature.

You want to control access by giving permissions on specific schemas.

You want to avoid name clashes when multiple teams create tables with the same name.

Syntax

PostgreSQL

CREATE SCHEMA schema_name;

-- To use objects in a schema:
SET search_path TO schema_name;

-- To create an object in a schema:
CREATE TABLE schema_name.table_name (...);

Schemas act like folders for database objects such as tables and views.

You can switch between schemas using SET search_path.

Examples

Creates a new schema named 'sales' to organize sales-related tables.

PostgreSQL

CREATE SCHEMA sales;

Creates a 'customers' table inside the 'sales' schema.

PostgreSQL

CREATE TABLE sales.customers (
  id SERIAL PRIMARY KEY,
  name TEXT
);

Sets the current schema to 'sales' so you can query 'customers' without schema prefix.

PostgreSQL

SET search_path TO sales;

SELECT * FROM customers;

Sample Program

This example creates an 'hr' schema, adds an 'employees' table, inserts two rows, then queries the table using the schema search path.

PostgreSQL

CREATE SCHEMA hr;

CREATE TABLE hr.employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO hr.employees (name) VALUES ('Alice'), ('Bob');

SET search_path TO hr;

SELECT * FROM employees;

Important Notes

If you don't specify a schema, objects go into the 'public' schema by default.

Use schemas to improve clarity and security by grouping related objects.

Remember to set the search path or use schema-qualified names to access objects.

Summary

Schemas group database objects to avoid name conflicts.

They help organize data by department, project, or purpose.

Use CREATE SCHEMA to make a schema and SET search_path to use it.