Data Modelling - Schemas in PostgreSQL

Introduction

In PostgreSQL, schemas are used to organize database objects like tables, views, and functions. Schemas provide a way to group related objects and can be used to manage permissions.

Creating a Schema

A schema in PostgreSQL is created using the CREATE SCHEMA statement. Here's an example:

CREATE SCHEMA myschema;
                    

CREATE SCHEMA
                    

This command creates a new schema named myschema.

Creating a Table within a Schema

To create a table within a specific schema, specify the schema name before the table name:

CREATE TABLE myschema.employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    salary NUMERIC
);
                    

CREATE TABLE
                    

This command creates a table named employees within the myschema schema.

Setting the Search Path

The search path determines the order in which schemas are searched when an object is referenced. You can set the search path using the SET command:

SET search_path TO myschema, public;
                    

SET
                    

This command sets the search path to first search myschema, then the default public schema.

Querying Data from a Table in a Schema

To query data from a table within a specific schema, specify the schema name before the table name:

SELECT * FROM myschema.employees;
                    

 id |     name      | position | salary 
----+---------------+----------+--------
(0 rows)
                    

This command retrieves all rows and columns from the employees table in the myschema schema.

Altering a Schema

You can alter a schema to rename it or change its owner. Here's how to rename a schema:

ALTER SCHEMA myschema RENAME TO newschema;
                    

ALTER SCHEMA
                    

This command renames the schema myschema to newschema.

Dropping a Schema

A schema can be removed from the database using the DROP SCHEMA statement. You can optionally use the CASCADE option to remove all objects within the schema:

DROP SCHEMA newschema CASCADE;
                    

DROP SCHEMA
                    

This command deletes the newschema schema and all objects contained within it.