Category: PostgreSQL

The 6 SERIAL Data Types in PostgreSQL

Posted on by Ian

The SERIAL data type in PostgreSQL is a pseudo-type used to create an auto-incrementing sequence of integers for a column. It is commonly used for primary keys, as it eliminates the need to manually assign unique identifiers for each new record. PostgreSQL handles this by automatically creating a sequence object that supplies a unique number each time a new row is inserted.

PostgreSQL provides three SERIAL types, each with two options for usage; which effectively equates to six different types. So basically, we have six options to choose from when creating a SERIAL column.

Continue reading →

Why it’s a Good Idea to Create Indexes on Foreign Keys in PostgreSQL

Posted on by Ian

When a foreign key is defined in a table, it ensures that the values in the column(s) correspond to values in a primary key or unique key in another table. While PostgreSQL automatically creates an index for primary keys (because these need to be fast for lookups and enforcing uniqueness), it does not automatically create an index for foreign keys.

Continue reading →

How to List all Domains in PostgreSQL

Posted on by Ian

In PostgreSQL, domains are basically data types with optional constraints. We can create them as a kind of user-defined data type, and then reuse them in our columns going forward.

As with any user-created object, we sometimes need to see a list of existing domains in a PostgreSQL database. Listing all domains can be useful for database management, documentation, or troubleshooting.

This article will show you how to retrieve a list of all domains in your PostgreSQL database.

Continue reading →

Create a Partial Index in PostgreSQL

Posted on by Ian

In PostgreSQL, a partial index is an index that’s built on a subset of the rows in a table. It includes only the rows that satisfy a given condition specified with the WHERE clause.

Partial indexes can help us achieve faster queries, reduced storage, and lower overhead for write-heavy workloads.

Below is a quick example that demonstrates how to create a partial index in PostgreSQL.

Continue reading →

Check How Many Times a Given Query Plan was Chosen for a Prepared Statement in PostgreSQL

Posted on by Ian

When a prepared statement is created in PostgreSQL, the server has two possible strategies for executing it: use a generic plan or use a custom plan. Each time we run a prepared statement it will chose one of these two plans.

By default, it will use a custom plan for the first five runs. After that it will decide whether or not it’s more efficient to use a generic plan for subsequent executions, or stay with a custom plan.

This begs the question; how do we check to see which plan PostgreSQL chose?

Continue reading →

Example of PostgreSQL Automatically Creating a Nested Composite Type

Posted on by Ian

Whenever we create a table in PostgreSQL, a composite type is automatically created behind the scenes. This composite type is based on the table that we created. Each column in the table becomes a field in the composite type.

If the table already uses a composite type, then the composite type that PostgreSQL creates will include that type in its definition, thereby creating a situation where we effectively have a nested composite type.

Continue reading →

A Deep Dive into PostgreSQL’s TRUNCATE Statement

Posted on by Ian

Most relational database management systems support the TRUNCATE statement, and PostgreSQL is no exception.

That said, PostgreSQL has a few differences in the way its TRUNCATE statement works when compared to many other RDBMSs.

In this article, we’ll explore the various features of PostgreSQL’s implementation of the TRUNCATE statement, along with examples to demonstrate.

Continue reading →