{"id":2315,"date":"2025-03-08T22:11:59","date_gmt":"2025-03-08T15:11:59","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2315"},"modified":"2025-03-08T22:14:02","modified_gmt":"2025-03-08T15:14:02","slug":"postgresql-schema","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-database-administration\/postgresql-schema\/","title":{"rendered":"PostgreSQL Schemas"},"content":{"rendered":"\n

Summary:<\/strong>\u00a0In this tutorial, you’ll learn about PostgreSQL schemas and how to use them to group database objects.<\/p>\n\n\n\n

Introduction to PostgreSQL schemas #<\/a><\/h2>\n\n\n\n

A schema is an object inside a database. A schema works like a folder that logically groups database objects like tables, views<\/a>, indexes<\/a>, sequences<\/a>, and functions<\/a>.<\/p>\n\n\n\n

A database may have multiple schemas whereas each schema belongs to a specific database.<\/p>\n\n\n\n

When you create a database<\/a>, PostgreSQL automatically creates a schema called\u00a0public<\/code>. The\u00a0public<\/code>\u00a0schema is the default schema of the database.<\/p>\n\n\n\n

If you create any database object like a table without specifying a schema name, PostgreSQL will put that object into the public<\/code> schema.<\/p>\n\n\n\n

Creating schemas #<\/a><\/h2>\n\n\n\n

PostgreSQL allows you to create a new schema using the CREATE SCHEMA<\/code> statement:<\/p>\n\n\n

CREATE<\/span> SCHEMA<\/span> schema_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the schema name after the CREATE SCHEMA<\/code> keywords.<\/p>\n\n\n\n
For example, the following statement creates a new schema called sales<\/code>:<\/p>\n\n\n
CREATE<\/span> SCHEMA<\/span> sales;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL lets you create database objects with the same name in different schemas.<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> sales.products(\n    id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>, \n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    price DEC<\/span>(11<\/span>,2<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To reference products<\/code> in the public<\/code> table, you use products<\/code>:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Or<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> public<\/span>.products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, when you reference the products<\/code> table in the sales<\/code> schema, you need to prefix the products<\/code> table with the sales<\/code> schema:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> sales.products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Schema search path #<\/a><\/h2>\n\n\n\n
When referencing a table (or any other objects) in a query, you don’t have to specify the schema name explicitly.<\/p>\n\n\n\n
The reason is that PostgreSQL uses the schema search path to determine the order of schemas to find the table.<\/p>\n\n\n\n
To check the current search path, you use the SHOW search_path<\/code> statement:<\/p>\n\n\n
SHOW<\/span> search_path;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
It usually returns the following by default:<\/p>\n\n\n
 search_path\n--------------<\/span>\n \"$user\", public<\/span>\n(1<\/span> row<\/span>)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
This means that PostgreSQL first looks for the schema with the same name as the current user (\"$user\"<\/code>). If it cannot find the object, it searches in the public<\/code> schema.<\/p>\n\n\n\n
To change the schema search path, you use the SET search_path<\/code> statement:<\/p>\n\n\n
SET<\/span> search_path TO<\/span> public<\/span>, sales;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement instructs PostgreSQL to search the objects in the public<\/code> schema first and then the sales<\/code> schema.<\/p>\n\n\n\n
Listing all schemas in a database #<\/a><\/h2>\n\n\n\n
To view all schemas in a database, you use the following query:<\/p>\n\n\n
SELECT<\/span> schema_name<\/span> \nFROM<\/span> information_schema.schemata;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 schema_name<\/span>\n--------------------<\/span>\n public<\/span>\n sales\n information_schema\n pg_catalog\n pg_toast<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The information_schema<\/code>, pg_catalog<\/code>, and pg_toast<\/code> are system schemas that store metadata.<\/p>\n\n\n\n
Granting privileges on a schema #<\/a><\/h2>\n\n\n\n
First, create a new role called elephant<\/code>:<\/p>\n\n\n
CREATE<\/span> ROLE<\/span> elephant\nWITH<\/span> LOGIN<\/span> PASSWORD<\/span> 'securepassword'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The role elephant<\/code> cannot access the sales<\/code> schema.<\/p>\n\n\n\n
GRANT<\/span> ALL<\/span>\nON<\/span> SCHEMA<\/span> sales\nTO<\/span> elephant;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now the role elephant can access the sales schema, create and drop database objects within the sales schema.<\/p>\n\n\n\n
Applications of PostgreSQL Schemas #<\/a><\/h2>\n\n\n\n
In application development, you can use schemas to organize, secure, and manage data effectively.<\/p>\n\n\n\n
Here are some typical applications of the schemas:<\/p>\n\n\n\n
Second, grant all privileges<\/a> on the\u00a0sales<\/code>\u00a0schema to the role\u00a0elephant<\/code>:<\/p>\n\n\n
For example, if you have a table called\u00a0products<\/code>\u00a0in the\u00a0public<\/code>\u00a0schema, you can create a new table<\/a> with the same name in the\u00a0sales<\/code>\u00a0schema:<\/p>\n\n\n