Public schema vs custom schemas in PostgreSQL - Hands-On Comparison

Understanding Public Schema vs Custom Schemas in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a small company. You want to organize your tables better by using schemas. By default, PostgreSQL uses the public schema, but you want to learn how to create and use a custom schema to keep your data organized.

🎯 Goal: Learn how to create a custom schema, create tables inside both the public schema and the custom schema, and understand how to query data from both schemas.

📋 What You'll Learn

Create a table in the default public schema

Create a custom schema named sales

Create a table inside the sales schema

Query data from both public and sales schemas

💡 Why This Matters

🌍 Real World

Organizing database objects into schemas helps keep data structured and easier to manage, especially in larger projects or multi-team environments.

💼 Career

Database administrators and developers often create and manage schemas to separate data logically, control access, and improve clarity in complex databases.

1

Create a table in the public schema

Write a SQL statement to create a table called customers in the default public schema with columns id (integer primary key) and name (text).

PostgreSQL

-- Create the customers table in the public schema
-- Your code here

Need a hint?

Use CREATE TABLE public.customers to specify the table in the public schema.

2

Create a custom schema named sales

Write a SQL statement to create a new schema called sales in the database.

PostgreSQL

CREATE TABLE public.customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);
-- Create the sales schema below
-- Your code here

Need a hint?

Use CREATE SCHEMA sales; to create the custom schema.

3

Create a table inside the sales schema

Write a SQL statement to create a table called orders inside the sales schema with columns order_id (integer primary key) and customer_id (integer).

PostgreSQL

CREATE TABLE public.customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE SCHEMA sales;
-- Create the orders table inside sales schema below
-- Your code here

Need a hint?

Use CREATE TABLE sales.orders to create the table inside the sales schema.

4

Query data from both schemas

Write two SQL SELECT statements: one to select all columns from customers in the public schema, and one to select all columns from orders in the sales schema.

PostgreSQL

CREATE TABLE public.customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE SCHEMA sales;

CREATE TABLE sales.orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER
);
-- Select all from public.customers
-- Your code here

-- Select all from sales.orders
-- Your code here

Need a hint?

Use SELECT * FROM public.customers; and SELECT * FROM sales.orders; to query both tables.