PostgreSQL - Schema

A PostgreSQL schema is a logical container that organizes database objects such as tables, views, indexes, functions and sequences within a database. It helps group related objects and manage them efficiently.

• Stores related database objects in a single namespace.
• Enables better control over user access and permissions.

Syntax

Create a Schema

CREATE SCHEMA schema_name;

Create a Table in a Schema

CREATE TABLE schema_name.table_name (
    column_name data_type,
    ...
);

Delete a Schema

DROP SCHEMA schema_name;

Delete a Schema with All Objects

DROP SCHEMA schema_name CASCADE;

Where:

• schema_name: The name of the schema.
• table_name: The table to be created inside the schema.
• CASCADE: Removes the schema along with all the objects it contains.

Working

Example 1: Create a Schema

The following query creates a schema named sales.

Query:

CREATE SCHEMA sales;

Output:

• The schema can now store tables, views, functions and other database objects.

Example 2: Create a Table in a Schema

The following query creates an orders table inside the sales schema.

Query:

CREATE TABLE sales.orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    amount NUMERIC
);

INSERT INTO sales.orders VALUES
(101, 'James Carter', 2500),
(102, 'Emily Johnson', 1800),
(103, 'Michael Brown', 3200);

• The table can be accessed using its qualified name sales.orders.

Example 3: Retrieve Data from a Schema Table

The following query retrieves all records from the Orders table.

Query:

SELECT * FROM sales.orders;

Output:

• The schema name (sales) is specified before the table name.
• PostgreSQL locates the table within the sales schema and returns its records.

Example 4: Drop a Schema

The following query removes the sales schema and all objects stored within it.

Query:

DROP SCHEMA sales CASCADE;

Output:

• The CASCADE option removes the schema along with all dependent objects.
• Without CASCADE, PostgreSQL returns an error if the schema contains database objects.