September 23, 2023
Summary: in this tutorial, you are going to get familiar with the most common server and database objects provided by PostgreSQL. It is important to understand those objects and their functionality so you do not miss out on the cool features that you may wish to have in the system.
Table of Contents
After installing PostgreSQL, loading sample database and connecting to the database server using pgAdmin GUI application, you will see that PostgreSQL provides many server and database objects. To leverage the features of each object that PostgreSQL provides effectively, you should have a good understanding of what each object is and how to use it effectively.
Let’s get familiar with these PostgreSQL server and database objects.
Server service
When you install a PostgreSQL instance, you will have a corresponding PostgreSQL server service. The PostgreSQL server service is also known as the PostgreSQL server. You can install multiple PostgreSQL servers on a physical server using different ports and having different locations to store data.
Databases
A database is a container of other objects such as tables, views, functions, and indexes. You can create as many databases as you want inside a PostgreSQL server.
Tables
Tables store data. A table belongs to a database and each database has multiple tables.
A special feature of PostgreSQL is table inheritance, meaning that a table (child table) can inherit from another table (parent table) so when you query data from the child table, the data from the parent table is also showing up.
Schemas
A schema is a logical container of tables and other objects inside a database. Each PostgreSQL database may have multiple schemas.
Tablespaces
Tablespaces are where PostgreSQL stores the data physically. Tablespaces allow you to move your data to different physical locations across drivers easily by using simple commands.
By default, PostgreSQL provides you with two tablespaces:
- The
pg_default
is for storing user data. - The
pg_global
is for storing system data.
The following picture shows the default tablespaces:
Views
Views are named queries stored in the database. Besides the read-only views, PostgreSQL supports updatable views.
Functions
A function is a reusable block of SQL code that returns a scalar value of a set of rows.
Operators
Operators are symbolic functions. PostgreSQL allows you to define custom operators.
Casts
Casts enable you to convert one data type into another data type. Casts backed by functions to perform the conversion. You can also create your casts to override the default casting provided by PostgreSQL.
Sequence
Sequences are used to manage auto-increment columns defined in a table as a serial column or an identity column.
Extension
PostgreSQL introduced extension concept since version 9.1 to wrap other objects including types, casts, indexes, functions, etc., into a single unit. The purpose of extensions is to make it easier to maintain.
In this tutorial, you have learned the common PostgreSQL database and server objects. Just take a few minutes to explore these objects to get a brief overview of them before starting the next tutorial.