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.

Practice

1. What is the main purpose of creating schemas in PostgreSQL?

easy

A. To encrypt the database tables

B. To speed up query execution automatically

C. To create backups of the database

D. To group database objects and organize them logically

Solution

1. Step 1: Understand what schemas do

   Schemas help organize tables, views, and other objects into groups.

2. Step 2: Identify the main purpose

   This organization helps keep the database tidy and manageable.

3. Final Answer:

   To group database objects and organize them logically -> Option D

4. Quick Check:

   Schemas = Organize objects [OK]

Hint: Schemas group objects to keep your database tidy [OK]

Common Mistakes:

• Thinking schemas speed up queries automatically
• Confusing schemas with backups
• Assuming schemas encrypt data

2. Which of the following is the correct syntax to create a new schema named sales?

easy

A. NEW SCHEMA sales;

B. CREATE SCHEMA sales;

C. MAKE SCHEMA sales;

D. CREATE DATABASE sales;

Solution

1. Step 1: Recall the command to create schemas

   The correct SQL command is CREATE SCHEMA schema_name;.

2. Step 2: Match the syntax

   CREATE SCHEMA sales; matches this syntax exactly for schema sales.

3. Final Answer:

   CREATE SCHEMA sales; -> Option B

4. Quick Check:

   CREATE SCHEMA = create schema [OK]

Hint: Use CREATE SCHEMA followed by the schema name [OK]

Common Mistakes:

• Using MAKE or NEW instead of CREATE
• Confusing CREATE SCHEMA with CREATE DATABASE
• Missing the semicolon at the end

3. Given the following commands:

CREATE SCHEMA marketing;
SET search_path TO marketing;
CREATE TABLE campaigns (id SERIAL PRIMARY KEY, name TEXT);

What schema will the table campaigns belong to?

medium

A. marketing

B. None, it will cause an error

C. default

D. public

Solution

1. Step 1: Understand SET search_path effect

   Setting search_path to marketing means new objects are created in that schema by default.

2. Step 2: Check where the table is created

   The CREATE TABLE campaigns command creates the table inside the current schema in search_path, which is marketing.

3. Final Answer:

   marketing -> Option A

4. Quick Check:

   SET search_path = schema for new objects [OK]

Hint: SET search_path sets default schema for new tables [OK]

Common Mistakes:

• Assuming tables go to public schema by default
• Thinking SET search_path only affects queries, not creation
• Believing this causes an error without schema prefix

4. You run the command SET search_path TO sales; but get an error: ERROR: schema "sales" does not exist. What is the most likely cause?

medium

A. The schema sales was not created before setting search_path

B. You need to use USE sales; instead

C. The database connection is closed

D. The table sales does not exist

Solution

1. Step 1: Understand error meaning

   The error says the schema sales does not exist, so PostgreSQL cannot set search_path to it.

2. Step 2: Identify cause

   This usually means the schema was never created with CREATE SCHEMA sales;.

3. Final Answer:

   The schema sales was not created before setting search_path -> Option A

4. Quick Check:

   SET search_path needs existing schema [OK]

Hint: Create schema before setting search_path to it [OK]

Common Mistakes:

• Using USE instead of SET search_path in PostgreSQL
• Confusing tables with schemas
• Ignoring the error and continuing

5. You have two schemas: public and archive. You want to query a table named orders in archive without typing the full name every time. Which command will let you switch your session to use archive schema first, then public?

hard

A. USE archive, public;

B. ALTER SCHEMA archive SET search_path;

C. SET search_path TO archive, public;

D. SET schema TO archive, public;

Solution

1. Step 1: Recall how to set search_path for multiple schemas

   PostgreSQL uses SET search_path TO schema1, schema2; to set schema priority.

2. Step 2: Identify correct syntax

   SET search_path TO archive, public; correctly sets archive first, then public.

3. Final Answer:

   SET search_path TO archive, public; -> Option C

4. Quick Check:

   SET search_path sets schema order [OK]

Hint: Use SET search_path with comma-separated schemas [OK]

Common Mistakes:

• Using USE instead of SET search_path
• Trying ALTER SCHEMA to set search_path
• Using SET schema instead of SET search_path