Public schema vs custom schemas in PostgreSQL

Introduction

Schemas help organize database objects like tables and views. The public schema is the default place for these objects, while custom schemas let you group objects separately for clarity and control.

You want to keep all your tables in one common area for simple projects.

You need to separate data for different parts of an app to avoid confusion.

You want to control access by giving permissions only on certain groups of tables.

You are working with multiple teams and want each team to have its own space.

You want to avoid name conflicts by putting objects with the same name in different schemas.

Syntax

PostgreSQL

CREATE SCHEMA schema_name;
-- To create a custom schema

SET search_path TO schema_name, public;
-- To use a custom schema along with public

SELECT * FROM schema_name.table_name;
-- To query a table in a specific schema

The public schema is created automatically in every new database.

If you don't specify a schema, PostgreSQL uses the public schema by default.

Examples

Creates a table named users in the public schema (default).

PostgreSQL

CREATE TABLE public.users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

Creates a custom schema called sales and a table orders inside it.

PostgreSQL

CREATE SCHEMA sales;

CREATE TABLE sales.orders (
  order_id SERIAL PRIMARY KEY,
  amount NUMERIC
);

Sets the search path so you can query sales.orders without schema prefix.

PostgreSQL

SET search_path TO sales, public;

SELECT * FROM orders;

Explicitly queries the users table in the public schema.

PostgreSQL

SELECT * FROM public.users;

Sample Program

This creates a custom schema hr, adds an employees table, inserts two rows, and selects all rows.

PostgreSQL

CREATE SCHEMA hr;

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

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

SELECT * FROM hr.employees;

Important Notes

Using custom schemas helps keep your database organized and secure.

Remember to set the search_path if you want to avoid typing schema names every time.

The public schema is convenient but can get cluttered in big projects.

Summary

The public schema is the default place for database objects.

Custom schemas let you group objects for better organization and access control.

You can switch between schemas using the search_path setting.