Public schema vs custom schemas in PostgreSQL - Visual Side-by-Side Comparison

Concept Flow - Public schema vs custom schemas

Start: Connect to DB

↓

Check default schema

↓

Public schema exists?

Yes→Use public schema

↓

Create custom schema

↓

Create public schema

↓

Use custom schema

↓

Create objects in schema

↓

Query objects with schema prefix or search_path

↓

End

This flow shows how PostgreSQL uses the public schema by default, but you can create and use custom schemas to organize database objects separately.

Execution Sample

PostgreSQL

CREATE SCHEMA sales;
CREATE TABLE sales.orders (id SERIAL PRIMARY KEY, amount INT);
INSERT INTO sales.orders (amount) VALUES (100);
SELECT * FROM sales.orders;

This code creates a custom schema 'sales', adds a table 'orders' inside it, inserts a row, and selects data from it.

Execution Table

Step	Action	Schema Affected	Object Created/Modified	Result/Output
1	CREATE SCHEMA sales;	sales	Schema 'sales' created	Success
2	CREATE TABLE sales.orders (id SERIAL PRIMARY KEY, amount INT);	sales	Table 'orders' created	Success
3	INSERT INTO sales.orders (amount) VALUES (100);	sales	Row inserted into 'orders'	1 row affected
4	SELECT * FROM sales.orders;	sales	Query executed	[{id:1, amount:100}]
5	SELECT * FROM orders;	public	Query executed	Error: relation "orders" does not exist
6	SET search_path TO sales;	session	Search path changed	Success
7	SELECT * FROM orders;	sales	Query executed	[{id:1, amount:100}]

💡 Execution stops after successful queries and error when querying non-existent table in public schema.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7
search_path	public	public	public	public	public	public	sales	sales
schemas	public	public, sales	public, sales	public, sales	public, sales	public, sales	public, sales	public, sales
tables in sales	none	none	orders	orders	orders	orders	orders	orders
tables in public	none	none	none	none	none	none	none	none

Key Moments - 2 Insights

Why does 'SELECT * FROM orders;' fail before setting search_path to 'sales'?

Because by default, PostgreSQL looks in the 'public' schema, which has no 'orders' table. The 'orders' table exists only in the 'sales' schema, so the query fails until search_path includes 'sales' (see execution_table step 5 and 7).

What happens when you create a table without specifying a schema?

It is created in the 'public' schema by default. This is why 'public' is the default schema unless you specify another or change the search_path.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What data is returned by 'SELECT * FROM sales.orders;'?

A Empty result

B [{id:1, amount:100}]

C Error: relation does not exist

D [{id:0, amount:0}]

💡 Hint

Check the 'Result/Output' column for step 4 in the execution_table.

At which step does the search_path change to include the 'sales' schema?

A Step 6

B Step 3

C Step 5

D Step 7

💡 Hint

Look at the 'Action' and 'Schema Affected' columns in execution_table for when search_path changes.

If you run 'SELECT * FROM orders;' without setting search_path to 'sales', what happens?

A Returns rows from sales.orders

B Returns empty result

C Error: relation 'orders' does not exist

D Returns rows from public.orders

💡 Hint

See execution_table step 5 for the query without schema prefix or search_path change.

Concept Snapshot

PostgreSQL uses 'public' schema by default.
Custom schemas organize objects separately.
Create schema with CREATE SCHEMA name;
Use schema-qualified names: schema.table
Change search_path to set default schema order.
Queries look in schemas by search_path order.

Full Transcript

In PostgreSQL, the default schema is 'public'. When you create tables without specifying a schema, they go into 'public'. You can create custom schemas to organize your tables and other objects separately. For example, creating a 'sales' schema and putting an 'orders' table inside it. By default, queries look in the 'public' schema unless you specify the schema name or change the search_path. If you query a table without schema prefix and it doesn't exist in 'public', you get an error. Changing the search_path to include your custom schema lets you query tables there without prefix. This helps keep database objects organized and avoid name conflicts.