PostgreSQL Tutorial

  • Home
  • Administration
  • Views
  • Triggers
  • Stored Procedures
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
Home / PostgreSQL Tutorial / PostgreSQL Server and Database Objects

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 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.

PostgreSQL Server Database Objects

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.

PostgreSQL Server

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.

PostgreSQL databases

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.

PostgreSQL tables

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.

postgresql schema

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.

PostgreSQL Tablespaces

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.

PostgreSQL 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.

PostgreSQL functions

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.

PostgreSQL Sequences

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.

PostgreSQL Extensions

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.

Previous Tutorial: Connect to PostgreSQL Database
Next Tutorial: PostgreSQL SELECT

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete
  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • PostgreSQL Recursive View
  • Learn PostgreSQL Recursive Query By Example
  • Creating Updatable Views Using the WITH CHECK OPTION Clause
  • PostgreSQL Upsert Using INSERT ON CONFLICT statement
  • How to Generate a Random Number in A Range
  • Using PostgreSQL ADD COLUMN to Add One or More Columns To a Table
  • PostgreSQL Character Types: CHAR, VARCHAR, and TEXT
  • Using PostgreSQL SERIAL To Create Auto-increment Column
  • PostgreSQL Boolean Data Type with Practical Examples
  • Understanding PostgreSQL Timestamp Data Types

More Tutorials

  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Functions
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2016 by PostgreSQL Tutorial Website. All Rights Reserved.