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 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. In order 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. It is also known as PostgreSQL server. You can install multiple PostgreSQL servers on a physical server using different ports and having different locations to store data.
Database
A database is a container of other objects such as tables, views, functions, indexes, etc. You can create as many databases as you want inside a PostgreSQL server.
Table
The table is used to store the data. You can have many tables in a database. A special feature of PostgreSQL table is inheritance. Meanings a table (child table) can inherit from another table (parent table) so when you query data from the child table, the data from parent table is also showing up.
Schema
A schema is a logical container of tables and other objects inside a database. Each PostgreSQL database may have multiple schemas. It is important to note that schema is a part of ANSI-SQL standard.
Tablespace
A tablespace is where PostgreSQL stores the data. PostgreSQL tablespace enables you to move your data to different physical location across drivers easily by using simple commands. By default, PostgreSQL provides two tablespaces: pg_defaultfor storing user’s data and pg_globalfor storing system data.
View
The view is a virtual table that is used to simplify complex queries and to apply security for a set of records. PostgreSQL also provides you with updatable views.
Function
The function is a block reusable SQL code that returns a scalar value of a list of records. In PostgreSQL, functions can also return composite objects.
Operator
The operator is a symbolic function. PostgreSQL allows you to define custom operators.
Cast
Casts enable you to convert one data type into another data type. Casts actually backed by functions to perform the conversion. You can also create your own casts to override the default casting provided by PostgreSQL.
Sequence
Sequences are used to manage auto-increment columns that defined in a table as a serial 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, we have introduced you to the most common PostgreSQL database and server objects. Just take a few minutes to explore those objects to get a brief overview before moving on to the next tutorial.