Creating and switching schemas in PostgreSQL - Try It Yourself

Creating and Switching Schemas in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a small company. You want to organize data by creating separate schemas for different departments. This helps keep data tidy and easy to find.

🎯 Goal: Learn how to create a new schema and switch to it so you can create tables inside that schema.

📋 What You'll Learn

Create a new schema named sales

Switch the current schema search path to sales

Create a table named customers inside the sales schema

The customers table should have columns id (integer) and name (text)

💡 Why This Matters

🌍 Real World

Organizing database objects into schemas helps keep data separated by department or function, making management easier.

💼 Career

Database administrators and developers often create and switch schemas to manage complex databases efficiently.

1

Create the sales schema

Write a SQL statement to create a new schema called sales.

PostgreSQL

-- Your code here to create the sales schema

Need a hint?

Use the CREATE SCHEMA command followed by the schema name.

2

Switch to the sales schema

Write a SQL statement to set the search path to the sales schema so that new tables will be created there by default.

PostgreSQL

CREATE SCHEMA sales;
-- Your code here to set the search path to sales

Need a hint?

Use the SET search_path TO command with the schema name.

3

Create the customers table in sales

Write a SQL statement to create a table named customers with columns id as integer and name as text. This table should be created inside the current schema, which is sales.

PostgreSQL

CREATE SCHEMA sales;
SET search_path TO sales;
-- Your code here to create the customers table

Need a hint?

Use CREATE TABLE customers (id INTEGER, name TEXT); to create the table.

4

Verify the current schema search path

Write a SQL statement to show the current schema search path to confirm it is set to sales.

PostgreSQL

CREATE SCHEMA sales;
SET search_path TO sales;
CREATE TABLE customers (id INTEGER, name TEXT);
-- Your code here to show the current search path

Need a hint?

Use the SHOW search_path; command to display the current schema search path.