The \di command can be used to list indexes in the current database when using psql (PostgreSQL’s command-line tool). This meta-command can display all indexes, or just indexes that match certain patterns. Here’s an in-depth explanation of how \di works, its options, and examples of its various uses.
Object-Relational
How to List all Domains in PostgreSQL
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.
Create a Partial Index in PostgreSQL
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.
Check How Many Times a Given Query Plan was Chosen for a Prepared Statement in PostgreSQL
When a prepared statement is created in PostgreSQL, the server has two possible strategies for executing it: use a generic query 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?
Example of PostgreSQL Automatically Creating a Nested Composite Type
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.
A Deep Dive into PostgreSQL’s TRUNCATE Statement
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.
Updating Composite Values in PostgreSQL
When we have a column with composite data in PostgreSQL, we can access each field by using dot notation. This is true whether we select the data or update it. This means that we can use dot notation to update individual fields within a composite column.
And if we need to update all fields at once, we can use the ROW constructor.
How to Pad a String with Specific Characters in PostgreSQL
PostgreSQL provides us with a couple of functions that allow us to pad strings. We can use these functions to put one or more space characters or other characters on either the right side, left side, or both sides of the string.
3 Ways to Concatenate Strings in PostgreSQL
When working with databases (and software in general), string concatenation is the operation of joining character strings end-to-end. For example if we have two words, we can combine them into one.
PostgreSQL provides us with multiple ways to concatenate strings. Below are two functions and one operator that we can use to concatenate strings in PostgreSQL.
UNIQUE Index vs UNIQUE Constraint in PostgreSQL: Which to Use?
In PostgreSQL, both UNIQUE indexes and UNIQUE constraints can be used to enforce uniqueness on columns.
So you may be wondering; if that’s true, why have both? And are there any circumstances that we should choose one over the other?
Let’s find out.