PostgreSQL: Schemas and the CREATE SCHEMA Statement [Examples]

This article will explain what PostgreSQL schemas are, and show you how to use them using the CREATE SCHEMA statement, as well as showing some example code.

What is a Schema in PostgreSQL?

In PostgreSQL, a schema is an entity within a database that holds other database objects such as tables, views, and sequences. They are an organizational unit that is used to group objects together and can also be used to manage permissions for them collectively.

How to Create a PostgreSQL Schema using CREATE SCHEMA

The CREATE SCHEMA statement is used to create a schema. It is used as follows:

CREATE SCHEMA schema_name;

The above SQL statement will create a new schema called schema_name (You’ll probably want to choose a name more suitable to your purpose) inside the current database.

Creating Objects Inside a Schema/Adding Objects to a Schema

Once a schema has been created, objects can be added to it:

CREATE TABLE schema_name.table_name (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    ...
);

Above, a table is created inside the schame schema_name using the CREATE TABLE statement.

PostgreSQL Schema Syntax/Using Schemas

The syntax for interacting with objects inside PostgreSQL schemas is quite simple – you simply prepend the schema name to the object inside it that you are trying to access:

SELECT * FROM schema_name.table_name;

Changing the Default Schema/Schema in Use

The default schema in a PostgreSQL does not need to have it’s name specified when executing queries. Any query executed without a schema specified will be executed in the default schema.

The default schema is called public, but this can be changed by using the SET statement:

SET search_path TO schema_name;    

Above, the default schema is changed by altering the search_path – all future queries will be executed in the specified schema – so, for example, using CREATE TABLE without specifying a schema will create it not in the public schema, but in the one specified by the SET statement.

You can show the default schema by running:

SHOW search_path;

Removing Objects from a Schema

Objects are removed from a schema the same way they are deleted when they are not in a schema, using the appropriate DROP statement and specifying the schema:

DROP TABLE schema_name.table_name;

Schema Permissions

You can manage user and role permissions for a schema using the GRANT statement as you would any other database object:

GRANT ALL ON SCHEMA schema_name TO user_name;

Above, all permissions for all objects inside schema_name are granted to a user.

Schemas are a useful tool for complex databases, allowing you to keep them organised based on who needs to access them, or their purpose.