Prepared statements are a precompiled SQL queries that we can execute multiple times with different arguments. They are limited to the current session, so we can only create them and run them from within the same session.
Sometimes we might want to review how many prepared statements we’ve created. Or we might need to deallocate one, but we’ve forgotten its name.
No problem. We can use the pg_prepared_statements view to see all of our prepared statements.
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.
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 readingThere are several ways that we can list all indexes in PostgreSQL. These include utilizing various system catalog views and psql commands. Below are five options for returning a list of indexes in PostgreSQL, along with examples of how to filter the results by index name, table name, and other criteria.
If you’ve been working with prepared statements in PostgreSQL, you may get to a point where you no longer need one or more of them. In that case, you might want to remove them, or delete them, drop them, or however you want to say it.
Fortunately, this is one of the quickest, easiest things to do in PostgreSQL.
Continue readingThe \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.
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 readingIn 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 readingWhen 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 readingWhenever 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