A Joel Lee's ʕ•ᴥ•ʔ Blog

Learnings From The First Ten Chapters of the Postgres Manual

Why read the manual?

I recently started reading the Postgres 14 manual as part of a reading group. My work heavily involves the use of Postgres and I wanted to better understand how to use Postgres. As a whole, I also felt that it would also give me a better overview of how people think about database system design.

An Overview of the first ten chapters

The first few chapters give an overview of Postgres type system. SQL is intended to be a strongly typed language and Postgres makes full use of this. The first ten to fifteen chapters give broad strokes of types as well as some of the commonly used functions. I picked out five points for further discussion.

1. Inheritance in Postgres tables

Why this is interesting to me: While common in most programming languages with an Object Oriented paradigm, I was a tad bit surprised to find it in a database system. Additionally, it also seems to support multiple inheritance.

Here’s an example which is a modified version of a thread I found on StackOverflow

CREATE TABLE vehicle ( name text );
CREATE TABLE boat (wheels int) INHERITS (vehicle);
CREATE TABLE car (engine text) INHERITS (vehicle);

CREATE TABLE boatcar () INHERITS (boat,car);

INSERT INTO boatcar (name) VALUES ('amphibious car');

If you define two tables: boat and car with distinct properties `wheels` and engine` and then subsequently decide to subclass them to get a amphibious vehicle called boatcar. In doing so you will have a table which looks like this:

name wheels engine
amphibious car NULL NULL

You can give it a try for yourself in this fiddle.

2. Parser and the Type System

Why this is interesting to me: I once took a language design class which showed the difficulty of doing type inference in a robust manner. As such, I was quite impressed to see that Postgres 14 supports JSON, Money, and even CIDR. Consequently, you can use perform quite a few networking tasks and view some of Postgres’s quirky operators at the same time. The >>= operator allows you to see if one subnet is contained another. It has the following semantics:

inet »= inet → boolean

You can fetch the broadcast address:

broadcast ( inet ) → inet

or even compute the smallest network intersection that includes both intersections like:

inet_merge(inet ‘192.168.1.5/24’, inet ‘192.168.2.5/24’) →
192.168.0.0/22

3. Time related functions

Why this is interesting to me: In times of desperate need, I will often head to Google to do timezone conversions. This often looks like a query of this form: “What time is it in Africa right now”

Through reading the manual, I realized that most of my usual queries can be done in Postgres as well, which works out great since my day job makes heavy use of Postgres. Here are some examples:

Day finder

Google Version: “What Day is it on 30th April”

SQL Version: SELECT EXTRACT(DOW FROM TIMESTAMP ‘2022-04-30’);

date_part
6

View on DB Fiddle

Timezone conversion

Google Version: ‘What time is it in Chicago’(Assuming one is in Tokyo) SQL Version: SELECT timezone(‘Indian/Mauritius’, current_timestamp);

Perhaps normal to some but it is novel to me. These aren’t the only notable features within the first few chapters. I’ve left out the use of Grouping Sets, table OIDs, and more.