PostgreSQL is an ORDBMS. What does that mean?

PostgreSQL is an ORDBMS, or an Object-Relational Database Management System. This means that, similar to Object-Oriented programming languages, objects, classes, and inheritance are supported by PostgreSQL.

10 months ago   •   3 min read

By Nick Arner

PostgreSQL is an ORDBMS, or an Object-Relational Database Management System. This means that, similar to Object-Oriented programming languages, objects, classes, and inheritance are supported by PostgreSQL.

In addition to having the characteristics of a relational database (in which data is stored in rows and tables in a database), PostgreSQL is capable of storing data as objects in a database.

Object-Oriented Models in a Database

In software development, objects are models that contain data (known as attributes, or properties), and code (known as procedures, or methods)

Objects allow for complex models to be developed in software. For example, you could have a “Cat” object that has the properties of a tail, and whiskers. Objects can be composed from other objects, and inherit the properties of those objects. So, if a Cat object inherits from an Animal object, the Cat will inherit all the properties of an Animal, but will retain all the characteristics that make it uniquely a cat.

Since PostgreSQL is able to store data as objects, developers are able to create complex objects to be stored and later retrieved in a PostgreSQL database.

Built-In PostgreSQL Data Types

PostgreSQL offers built-in data types that are more complex than a relational database like MySQL, including:

bigint

signed eight-byte integer

bigserial

auto incrementing eight-byte integer

bit [ (n) ]

fixed-length bit string

bit varying [ (n) ]

variable-length bit string

boolean

logical Boolean (true/false)

box

rectangular box on a plane

bytea

binary data ("byte array")

character [ (n) ]

fixed-length character string

character varying [ (n) ]

variable-length character string

cidr

IPv4 or IPv6 network address

circle

circle on a plane

date

calendar date (year, month, day)

double precision

double precision floating-point number (8 bytes)

inet

IPv4 or IPv6 host address

integer

signed four-byte integer

interval [ fields ] [ (p) ]

time span

json

textual JSON data

jsonb

binary JSON data, decomposed

line

infinite line on a plane

lseg

line segment on a plane

macaddr

MAC (Media Access Control) address

money

currency amount

numeric [ (p, s) ]

exact numeric of selectable precision

path

geometric path on a plane

pg_lsn

PostgreSQL Log Sequence Number

point

geometric point on a plane

polygon

closed geometric path on a plane

real

single precision floating-point number (4 bytes)

smallint

signed two-byte integer

smallserial

auto incrementing two-byte integer

serial

auto incrementing four-byte integer

text

variable-length character string

time [ (p) ] [ without time zone ]

time of day (no time zone)

time [ (p) ] with time zone

time of day, including time zone

timestamp [ (p) ] [ without time zone ]

date and time (no time zone)

timestamp [ (p) ] with time zone

date and time, including time zone

tsquery

text search query

tsvector

text search document

txid_snapshot

user-level transaction ID snapshot

uuid

universally unique identifier

xml

XML data


Extending Data Types

Because of its support for Object-Oriented characteristics, PostgreSQL makes it possible to extend data types to create custom data types. This allows for a high level of flexibility for developers working with complex data models tasks that need to integrate with a database. These custom types inherit all of the properties of the object that they are based off of.

So, if you create a custom data type based off of two existing, parent data types; the custom data type will have whatever new properties have been added to it by the developer, as well as all of the properties of the two parent data types.

Support for Complex Data Allows for Complex Applications

Since PostgreSQL allows for data models to be created through Object-Oriented properties, deve lopers can create custom data objects which facilitate the development of complex applications, whose behavior can only be modeled through these custom objects.

Table Inheritance

The Object-Oriented nature of PostgreSQL also includes table-inheritance. A child table can inherit columns from a parent table; so that the child table has all the columns that a parent table has, in addition to the new columns that make the child table distinct to itself.

An example of  this, from the PostgreSQL documentation, would be a table of CAPITALS that inherits from a table of CITIES:

The Object-Oriented nature of PostgreSQL also includes table-inheritance. A child table can inherit columns from a parent table; so that the child table has all the columns that a parent table has, in addition to the new columns that make the child table distinct to itself.

Soft Deletion

Another feature supported by PostgresSQL is Soft Deletion. Hard Deletion is when rows in a database get deleted using `DELETE FROM table WHERE …`.

Soft Deletion occurs when rows in the database are deleted using `UPDATE table SET deleted_at = now() WHERE …`.

The Soft Deletion feature allows for data to be easily recovered if it gets accidentally deleted by the user; a benefit over Hard Deletion.

Spread the word

Keep reading