Summary: in this tutorial, you will learn what a PostgreSQL schema is and how PostgreSQL uses the schema search path to resolve objects in schemas.
What is a schema in PostgreSQL
To access an object of a schema, you qualify its name with the schema name as a prefix:
Or you can set a search path which includes the schema. We will cover the schema search path later.
A database can contain one or multiple schemas while 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 several scenarios which 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 creates a schema named
public for every new database. Whatever object you create without specifying the schema name, PostgreSQL will automatically put it into this
public schema. Therefore, the following statements are equivalent:
CREATE TABLE table_name(...);
CREATE TABLE public.table_name(...);
The PostgreSQL schema search path
We often refer to a table without schema name e.g.,
staff table instead of a fully qualified name such as
When we refer to a table using just its name, PostgreSQL searches for the table by using something called a 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. The
current_schema() function returns the current schema:
Here is the output:
This is why PostgreSQL uses
public for every new object that you create.
To view the current search path, you use the following
The output is as follows:
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;
The 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 object in the schemas, which does not belong to them. To enable users to access the objects in the schema that they do not own, you must grant the
USAGE privilege to the users on the schema as shown in the following statement:
GRANT USAGE ON SCHEMA schema_name TO user_name;
To allow users to create objects in the schema that they do not own, you need to grant them the
CREATE privilege on the schema.
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 PostgreSQL uses the search path to resolve object names.