PostgreSQL Schemas

A schema is a namespace inside a database that holds tables, views, functions, and other objects. Where MongoDB has Database → Collection, PostgreSQL adds an extra layer: Database → Schema → Table.

The Default public Schema

Every new database comes with a schema named public. When you write CREATE TABLE employees (...) without specifying a schema, it's created as public.employees.

SELECT * FROM employees;
-- shorthand for:
SELECT * FROM public.employees;

Why Use Schemas?

• Organize a large database — e.g. separate sales, hr, and analytics schemas within one database.
• Isolate different applications or tenants that share the same database.
• Avoid naming collisions — two schemas can each have a table named users without conflict.

Creating and Using a Schema

CREATE SCHEMA hr;

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

SELECT * FROM hr.employees;

Listing Schemas

\dn

The search_path

search_path decides which schema PostgreSQL looks in when you reference a table without a schema prefix.

SHOW search_path;
-- "$user", public

SET search_path TO hr, public;

SELECT * FROM employees;
-- now resolves to hr.employees first

Dropping a Schema

DROP SCHEMA hr;          -- fails if hr contains any tables
DROP SCHEMA hr CASCADE;  -- drops the schema AND everything inside it

Common Errors & Troubleshooting

• ERROR: relation "employees" does not exist, even though it clearly does — the table probably lives in a schema not currently on your search_path. Either qualify it explicitly (hr.employees) or update search_path.
• ERROR: schema "hr" does not exist — you tried to create a table in a schema before creating the schema itself; run CREATE SCHEMA hr; first.
• DROP SCHEMA hr; failing — a schema containing tables can't be dropped without CASCADE (which also deletes everything inside it) — make sure that's really what you want before adding CASCADE.

Key Takeaways

• Schemas sit between a database and its tables — the full path to a table is database.schema.table.
• Every table you create lands in public unless you specify a schema explicitly.
• search_path controls which schema is checked first for unqualified table names.