POSTGRESQL
SCHEMA

Schemas in PostgreSQL

intro

Let’s learn the definition of a Schema path in Postgres - we’ll tell you what it is, the ins and outs, and why it plays a pivotal role in the management and optimization of database applications - have a read!

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Schema: Definition

Schemas in PostgreSQL are logical containers that allow you to organize database objects such as tables, stored procedures, functions, etc. Schemas provide a way to rationally group related objects and separate them based on their functionalities or business domains. In other words, it is a conceptual space that contains named database objects such as tables, stored procedures, etc.

Schemas allow you to group related views, tables, operators, stored procedures, etc. making it easier to manage complex database structures.

In the following segment of this blog, we will look at why we need to use schemas.

Why Schemas?

Why do we need to use a schema? Let’s look at some reasons why schemas play very crucial roles in our database management.

  • Schemas allow you to organize database objects for easy maintenance. Since PostgreSQL supports the use of multiple schemas within one database, if we have a database X with two schemas (schema_1 and schema_2) and a table X1, accessing the table through two different schemas becomes easy.
  • It is easier to restore and back up data in a separate schema.

Schema Types in PostgreSQL

In the context of database management, schemas can be classified into different categories based on some factors: purpose, and access control. Public and custom schemas are two such types. Let’s dive into these two:

Public Schema

This is a shared schema type that is created by default when you initialize a PostgreSQL database without defining a name for the database. This means that for every object you create without specifying the schema name, PostgreSQL will put it in the public schema. Public schemas are accessible to all users, as they’re typically used for objects that need to be accessed and shared by multiple roles in a database.

Custom Schema

A custom schema, on the other hand, is a user-defined schema.  Custom schemas are not general-purpose schemas and are not accessible to all users by default, unlike public schemas. This implies that only authorized users can access this type of schema and the objects within them.

To create a custom schema, replace schema_name with your desired schema name and execute the following statement:

Running an SQL Statement in Dbvisualizer
Running an SQL Statement in Dbvisualizer

This will create a schema with the specified name in the schema tab of your database as shown in DbVisualizer below:

A Successfully Created Schema in DbVisualizer
A Successfully Created Schema in DbVisualizer

Great! 👏 we’ve been able to successfully create a custom schema in our PostgreSQL database. For a general overview of data organization in PostgreSQL, let us take a brief dive into how data is structured and arranged in PostgreSQL.

Structural Organization of Data in PostgreSQL

Data Organization in PostgreSQL
Data Organization in PostgreSQL

Although PostgreSQL has more complexity than this illustration above portrays, it is important to note that it illustrates the basic framework of how data is organized and structured in PostgreSQL. Let’s analyze each phase of the hierarchy:

  • PostgreSQL Cluster: The hierarchy's top level is this one. It is a group of databases controlled by a single PostgreSQL server instance that covers every set of data files, settings, and operations required to run one or more databases. A PostgreSQL cluster typically equates to a single installation of PostgreSQL servers.
  • Database: Multiple databases may exist inside a PostgreSQL cluster. For data, schemas, tables, and other database objects, each database serves as an independent storage space.
  • Schema: A schema is a method of organizing database items inside a database. Tables, views, functions, and other objects may be included in a schema. They help you manage and correctly organize your data.
  • Object: Database objects are the individual components that make up the database, such as tables, indexes, views, functions, and more. Objects are organized within schemas and are used to store, manipulate, and retrieve data.

Enough of the theory on PostgreSQL schemas. Let’s look at a particular use case in the chapter below:

Real-world Use Case

Much like file cabinets in a big office, schemas are frequently used in databases in situations that call for the systematic arrangement and separation of data. Let's look at a real-world example of a database schema to see how it is used.

Scenario

You are managing an e-commerce website's database, and you want to organize your product inventory separately from customer data. PostgreSQL schemas provide a powerful way to achieve this separation.

Copy
        
1 -- Create the "inventory" schema 2 CREATE SCHEMA inventory; 3 4 -- Create a table for storing product information in the "inventory" schema 5 CREATE TABLE inventory.products ( 6 product_id serial PRIMARY KEY, 7 product_name VARCHAR(255), 8 price DECIMAL(10, 2), 9 stock_quantity INT 10 ); 11 12 -- Create the "users" table in the default "public" schema to store customer information 13 CREATE TABLE public.users ( 14 user_id serial PRIMARY KEY, 15 username VARCHAR(50) UNIQUE, 16 email VARCHAR(100) UNIQUE, 17 password_hash VARCHAR(100) 18 ); 19 20 --Insert a product into the "inventory.products" table 21 INSERT INTO inventory.products (product_name, price, stock_quantity) 22 VALUES ('Laptop', 699.99, 50); 23 24 -- Insert a user into the "public.users" table 25 INSERT INTO public.users (username, email, password_hash) 26 VALUES ('janesally', 'janesally@techcontentmarketers.com', 'hashed_password'); 27 28 -- Query all products from the "inventory.products" table 29 SELECT * FROM inventory.products; 30 31 -- Query all users from the "public.users" table 32 SELECT * FROM public.users;

In the illustration above, we

  1. create two schemas: "public" and "inventory."
  2. create a table named "products" within the "inventory" schema to store product information.
  3. create a table named "users" in the default "public" schema to store customer information.
  4. insert a sample product and user into their respective tables.
  5. demonstrate how to query products from the "inventory.products" table and users from the "public.users" table.

What has been achieved in the end? It is now simpler to maintain and organize your e-commerce database since we have successfully segregated the product inventory from the customer data. This example demonstrates the usefulness of PostgreSQL schemas and how they may assist in keeping various types of data structured and separated inside a single database.

Outro

In this article, you have learned about what PostgreSQL schemas are and what they offer. Schemas offer a powerful means of organizing and managing database objects. By utilizing custom and default schemas, developers can achieve efficient organization of data, and fine-grained access control, amongst other useful benefits. Through proper schema design, developers can also build scalable applications that optimize performance and simplify maintenance, taking full advantage of the capabilities of their database, whether it would be PostgreSQL or another database of choice.

Make sure to follow the DbVisualizer’s The Table blog to learn more about database development and until next time.

FAQs

Can I have multiple schemas in a single PostgreSQL database?

Yes, you can have multiple schemas within a single PostgreSQL database. PostgreSQL creates the public schema by default, but you can create additional custom schemas to suit your needs. Multiple schemas help in separating and organizing database objects based on their purpose which improves the overall database structure and manageability.

What is the purpose of using custom schemas in PostgreSQL?

Custom schemas in PostgreSQL serve the purpose of organizing and managing database objects. They provide a way to group related tables, views, functions, and other objects logically. Custom schemas improve data organization, prevent naming conflicts, simplify maintenance, and enable fine-grained access control.

True or False: The 'public' schema in PostgreSQL is mandatory and cannot be removed.

True.

Like in many other database management systems, the public schema in PostgreSQL is created by default when initializing a new database and cannot be removed. It serves as a default schema and contains system objects and functions that are accessible to all users. While you cannot remove the public schema, you can still create additional custom schemas and organize your database objects within them.

Dbvis download link img
About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

PostgreSQL Upsert: INSERT ON CONFLICT Guide

author Antonello Zanini tags POSTGRESQL UPSERT 7 min 2024-03-25
title

Postgres TEXT vs VARCHAR: Comparing String Data Types

author Antonello Zanini tags POSTGRESQL TEXT VARCHAR 6 min 2024-03-14
title

Database Schema Design: A Comprehensive Guide for Beginners

author Ochuko Onojakpor tags SCHEMA 13 min 2024-03-07
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗