August 7, 2023
Summary: in this tutorial, you will learn about PostgreSQL schema and how to use the schema search path to resolve objects in schemas.
What is a PostgreSQL schema
To access an object in a schema, you need to qualify the object by using the following syntax:
A database can contain one or multiple schemas and each schema belongs to only one database. Two schemas can have different objects that share the same name.
For example, you may have
sales schema that has
staff table and the
public schema which also has the
staff table. When you refer to the
staff table you must qualify it as follows:
Why do you need to use schemas
There are some scenarios that you want to use schemas:
- 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.
PostgreSQL automatically creates a schema called
public for every new database. Whatever object you create without specifying the schema name, PostgreSQL will place it into this
public schema. Therefore, the following statements are equivalent:
CREATE TABLE table_name( ... );
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 as
When you reference a table using its name only, PostgreSQL searches for the table by using the schema 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 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.
current_schema() function returns the current schema:
Here is the output:
current_schema ---------------- public (1 row)
This is why PostgreSQL uses
public for every new object that you create.
To view the current search path, you use the
SHOW command in
The output is as follows:
search_path ----------------- "$user", public (1 row)
In this output:
"$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 the
postgresuser to login and access the
stafftable. PostgreSQL will search for the
stafftable in the
postgresschema. If it cannot find any object like that, it continues to look for the object in the
- The second element refers to the
publicschema as we have seen before.
To create a new schema, you use the
CREATE 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 named
staff without specifying the schema name, PostgreSQL will put this
staff table into the
CREATE TABLE staff( staff_id SERIAL PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE );
The following picture shows the new schema
sales and the
staff table that belongs to the
To access the
staff table in the
sales schema you can use one of the following statement:
SELECT * FROM staff;
SELECT * FROM sales.staff;
public schema is the second element in the search path, so to access the
staff 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 the
public schema using a fully qualified name:
SET search_path TO public;
public schema is not a special schema, therefore, you can drop 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 the
USAGE privilege of the schema to the users:
GRANT USAGE ON SCHEMA schema_name TO role_name;
To allow users to create objects in the schema that they do not own, you need to grant them the
CREATE privilege of the schema to the users:
GRANT CREATE ON SCHEMA schema_name TO user_name;
Note that, by default, every user has the
USAGE on the
PostgreSQL schema operations
- To create a new schema, you use the
- To rename a schema or change its owner, you use the
- To drop a schema, you use the
In this tutorial, you have learned about the PostgreSQL schema and how PostgreSQL uses the search path to resolve object names.