What are database schema in PostgreSQL?

Overview

In a database, a schema is an object container. Objects can be tables, data types, functions, or operators. There are many reasons for using schemas in a database cluster. Let's see some of them.

  • Schemas allow many users to use one database without interfering with each other.
  • Schemas organize database objects into logical groups to make them more manageable.
Database, schema, table, view and index

Note: A schema can be compared to a directory (at the OS level) except that it cannot be nested. It can also be seen as a namespace.

Working with schema

Every new database contains a default schema called public. To see it, use the command below:

\dn;

When we create a table (CREATE TABLE myTable(...)), we create it in the public schema (CREATE TABLE public.myTable(...)).

Basic operations

Let's see how to create a new schema. The syntax looks like this:

CREATE SCHEMA schema_name;

Next, let's see how to delete a schema. There are two options to consider here: empty schema (no object in the schema) and non-empty schema. If it is empty, we use DROP like this:

DROP SCHEMA schema_name;

In case the schema is not empty, we add CASCADE at the end of the syntax above:

CREATE SCHEMA schema_name CASCADE;

Exercises

Let's create new schemas below.

-- create two schemas for universities
CREATE SCHEMA unigom;
CREATE SCHEMA unikis;
-- view schemas
\dn;
  • Line 1–2: We create unigom and unikis schemas.
  • Line 6: We list all available schemas.

Next, let's create tables in each of those schemas. 

CREATE TABLE unigom.student (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(45),
    department VARCHAR(40),
    startDate DATE
);
CREATE TABLE unikis.lecture (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(45),
    speciality VARCHAR(40),
    startDate DATE
);
\dt unigom.*;
\dt unikis.*;

Using qualified name, we create the student table in unigom schema (line 1) and the lecture table in unikis schema (line 8).

Note: A qualified name consists of the schema name and table name separated by a dot.

Quiz

In the widget below, create the lecture table in unigom schema and the student table in the public schema.

-- your code goes here...

Let's move the student table from the public schema to unikis.

ALTER TABLE student
    SET SCHEMA unikis;
-- list tables in unikis schema
\dt unikis.*

Wrap up

Just like we have directories in our system to organize files, schemas let us organize tables in a database. The following are the main points to remember:

  • CREATE SCHEMA / DROP SCHEMA: Create a new schema / remove a schema.
  • Use object qualified name to write or access an object in a given schema.
  • ALTER TABLE ... SET SCHEMA ...: Move a table to another schema.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)