A database schema is a named logical collection of database objects. It groups tables, views, functions, triggers, and other objects under one name, much like a namespace or package. PostgreSQL creates the public schema by default, and unqualified objects and queries use it unless another schema is selected. section 2.1 section 2.1.2
Schemas help organize database structure and application boundaries:
- Map application modules or microservices to dedicated schemas, such as products, customers, and sales. section 2.1.2
- Avoid name conflicts when separate modules contain similarly named tables. section 2.1.2
- Keep related objects together while allowing queries to join tables across schemas within one database. section 2.1
- Use schemas when they improve application design and manageability; a single public schema can be sufficient for simpler cases. section 2.1.2
Create and use application schemas
CREATE SCHEMA products; CREATE SCHEMA customers; CREATE SCHEMA sales; SET search_path TO products; CREATE TABLE products.catalog ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price NUMERIC(10, 2) ); The qualified name products.catalog places the table in the products schema. Setting the search_path lets subsequent unqualified statements resolve against products instead of the default public schema. section 2.1.2 section 2.1.3
\dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
Run \dn in psql to inspect the schemas available in the connected database and verify the default public schema. section 2.1.2
