Creating and switching schemas in PostgreSQL - Visual Walkthrough

Concept Flow - Creating and switching schemas

Start

↓

Create Schema

↓

Switch Schema with SET search_path

↓

Run Queries in New Schema

↓

End

First, create a new schema. Then switch to it using SET search_path. Finally, run queries in the new schema.

Execution Sample

PostgreSQL

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

This code creates a schema named 'sales', switches to it, creates a table, inserts a row, and selects data.

Execution Table

Step	Command	Action	Result	Current Schema
1	CREATE SCHEMA sales;	Create new schema 'sales'	Schema 'sales' created	public
2	SET search_path TO sales;	Switch default schema to 'sales'	search_path set to 'sales'	sales
3	CREATE TABLE customers(...);	Create table 'customers' in 'sales'	Table 'customers' created	sales
4	INSERT INTO customers(name) VALUES ('Alice');	Insert row into 'customers'	1 row inserted	sales
5	SELECT * FROM customers;	Select all rows from 'customers'	Returns: id=1, name='Alice'	sales

💡 All commands executed successfully with schema switched to 'sales'.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5
search_path	public	public	sales	sales	sales	sales
schemas	public	public, sales	public, sales	public, sales	public, sales	public, sales
tables in sales	none	none	none	customers	customers with 1 row	customers with 1 row

Key Moments - 2 Insights

Why does the table 'customers' get created in the 'sales' schema after switching search_path?

Because after step 2, the search_path is set to 'sales', so all unqualified table names refer to that schema, as shown in execution_table step 3.

What happens if you don't switch the schema before creating the table?

The table would be created in the default 'public' schema, not in 'sales'. This is shown by the initial search_path being 'public' before step 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the current schema after step 2?

A sales

B public

C default

D none

💡 Hint

Check the 'Current Schema' column in execution_table row for step 2.

At which step is the table 'customers' created?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column in execution_table to find when 'customers' table is created.

If you remove the 'SET search_path TO sales;' command, where will the 'customers' table be created?

A In the 'sales' schema

B In the 'public' schema

C No table will be created

D In a temporary schema

💡 Hint

Refer to variable_tracker for initial search_path before step 2.

Concept Snapshot

CREATE SCHEMA schema_name;  -- creates a new schema
SET search_path TO schema_name;  -- switches default schema
Unqualified table names use current search_path schema
Use to organize database objects logically
Switching schema affects where tables are created and queried

Full Transcript

This visual trace shows how to create a new schema in PostgreSQL and switch to it. First, the CREATE SCHEMA command makes a new schema named 'sales'. Then, SET search_path TO sales changes the default schema for commands. After switching, creating a table without schema prefix puts it inside 'sales'. Inserting and selecting data works inside the switched schema. The variable tracker shows the search_path changing from 'public' to 'sales', and the tables appearing in the 'sales' schema. Key moments clarify why switching schema matters and what happens if you don't. The quiz tests understanding of schema switching effects.