PostgreSQL Server and Database Objects

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.

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:

1. The pg_default is for storing user data.
2. 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.