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.
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.
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.
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.
A schema is a logical container of tables and other objects inside a database. Each PostgreSQL database may have multiple schemas.
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:
pg_defaultis for storing user data.
pg_globalis for storing system data.
The following picture shows the default tablespaces:
A function is a reusable block of SQL code that returns a scalar value of a set of rows.
Operators are symbolic functions. PostgreSQL allows you to define custom operators.
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.
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.