Creating and switching schemas in PostgreSQL

Introduction

Schemas help organize database objects like tables into groups. Creating and switching schemas lets you keep things tidy and work in the right group.

You want to separate data for different projects in the same database.

You need to organize tables by department, like sales and marketing.

You want to avoid name conflicts by putting tables in different schemas.

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

You want to switch your work context to a different schema easily.

Syntax

PostgreSQL

CREATE SCHEMA schema_name;

SET search_path TO schema_name;

CREATE SCHEMA makes a new schema with the name you choose.

SET search_path changes your current schema so you can use objects there without full names.

Examples

This creates a new schema called sales.

PostgreSQL

CREATE SCHEMA sales;

This switches your current schema to sales. Now you can create or access tables there without prefixing sales..

PostgreSQL

SET search_path TO sales;

Create a marketing schema and switch to it immediately.

PostgreSQL

CREATE SCHEMA marketing;
SET search_path TO marketing;

Sample Program

This example creates a schema named test_schema, switches to it, creates a customers table, inserts two rows, and then selects all rows.

PostgreSQL

CREATE SCHEMA test_schema;
SET search_path TO test_schema;
CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO customers (name) VALUES ('Alice'), ('Bob');
SELECT * FROM customers;

Important Notes

When you switch schemas with SET search_path, you don't need to write the schema name before table names.

If you don't set the search path, PostgreSQL uses the default public schema.

Be careful: switching schemas only affects your current database session.

Summary

Schemas group database objects to keep things organized.

Create schemas with CREATE SCHEMA and switch using SET search_path.

Switching schemas helps you work in the right context without typing full names.