Introduction to PostgreSQL schema

In PostgreSQL, a schema is a named collection of database objects, including tables,views,indexes,data types,functions,stored procedures, and operators.

A schema allows you to organize and namespace database objects within a database.

To access an object in a schema, you need to qualify the object by using the following syntax:

schema_name.object_name

A database may contain one or more schemas. However, a schema belongs to only one database. Additionally, two schemas can have different objects that share the same name.

For example, you may havesales schema that hasstaff table and thepublic schema which also has thestaff table. When you refer to thestaff table you must qualify it as follows:

public.staff

Or

sales.staff

Schemas can be very useful in the following scenarios:

• Schemas allow you to organize database objects e.g., tables into logical groups to make them more manageable.
• Schemas enable multiple users to use one database without interfering with each other.

The public schema

PostgreSQL automatically creates a schema calledpublic for every new database. Whatever object you create without specifying the schema name, PostgreSQL will place it into thispublic schema. Therefore, the following statements are equivalent:

CREATETABLE table_name(  ...);

and

CREATE TABLE public.table_name(   ...);

The schema search path

In practice, you will refer to a table without its schema name e.g.,staff table instead of a fully qualified name such assales.staff table.

When you reference a table using its name only, PostgreSQL searches for the table by using theschema search path, which is a list of schemas to look in.

PostgreSQL will access the first matching table in the schema search path. If there is no match, it will return an error, even if the name exists in another schema in the database.

The first schema in the search path is called the current schema. Note that when you create a new object without explicitly specifying a schema name, PostgreSQL will also use the current schema for the new object.

Thecurrent_schema() function returns the current schema:

SELECTcurrent_schema();

Here is the output:

current_schema----------------public(1 row)

This is why PostgreSQL usespublic for every new object that you create.

To view the current search path, you use theSHOW command inpsql tool:

SHOW search_path;

The output is as follows:

search_path-----------------"$user", public(1 row)

In this output:

• The"$user" specifies that the first schema that PostgreSQL will use to search for the object, which has the same name as the current user. For example, if you use thepostgres user to log in and access thestaff table. PostgreSQL will search for thestaff table in thepostgres schema. If it cannot find any object like that, it continues to look for the object in thepublic schema.
• The second element refers to thepublic schema as we have seen before.

To create a new schema, you use theCREATE SCHEMA statement:

CREATE SCHEMA sales;

To add the new schema to the search path, you use the following command:

SET search_path TO sales, public;

Now, if you create a new table namedstaff without specifying the schema name, PostgreSQL will put thisstaff table into thesales schema:

CREATE TABLEstaff(    staff_id SERIAL PRIMARY KEY,    first_name VARCHAR(45) NOTNULL,    last_name VARCHAR(45) NOTNULL,    email VARCHAR(100) NOTNULL UNIQUE);

The following picture shows the new schemasales and thestaff table that belongs to thesales schema:

To access thestaff table in thesales schema you can use one of the following statements:

SELECT * FROM staff;

and

SELECT * FROM sales.staff;

Thepublic schema is the second element in the search path, so to access thestaff table in the public schema, you must qualify the table name as follows:

SELECT * FROM public.staff;

If you use the following command, you will need to explicitly refer to objects in thepublic schema using a fully qualified name:

SET search_path TOpublic;

Thepublic schema is not a special schema, therefore, you candrop it too.

PostgreSQL schemas and privileges

Users can only access objects in the schemas that they own. It means they cannot access any objects in the schemas that do not belong to them.

To allow users to access the objects in the schema that they do not own, you must grant theUSAGE privilege of the schema to the users:

GRANT USAGE ON SCHEMA schema_nameTO role_name;

To allow users to create objects in the schema that they do not own, you need to grant them theCREATE privilege of the schema to the users:

GRANT CREATE ON SCHEMA schema_nameTO user_name;

Note that, by default, every user has theCREATE andUSAGE on thepublic schema.

PostgreSQL schema operations

• To create a new schema, you use theCREATE SCHEMA statement.
• To rename a schema or change its owner, you use theALTER SCHEMA statement.
• To drop a schema, you use theDROP SCHEMA statement.

Summary

• A schema is a named collection of database objects, including tables, views, indexes, sequences, and so on.
• Use schemas to organize and namespace these objects within a database.
• Use the search path to resolve object names.

Last updated onFebruary 12, 2024

On this page

• Introduction to PostgreSQL schema
• The public schema
• The schema search path
• PostgreSQL schemas and privileges
• PostgreSQL schema operations
• Summary