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.
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.
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.
Continue readingWhen 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.
Continue readingIn 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.
Continue readingWhen using PostgreSQL’s psql command line tool, we can use the \dt command to get a list of tables. This meta-command can display all tables, or just tables that match certain patterns. Below is an overview of how \dt works, its options, and examples of its various uses.
In PostgreSQL, indexes on expressions (also known as “functional indexes”) allow you to create an index not just on a column but on the result of an expression or function applied to a column (or multiple columns). This can significantly improve query performance when the same expression is used frequently in queries, especially for complex calculations or transformations.
Continue readingPostgreSQL provides us with a few functions that we can use to convert strings between uppercase and lowercase.
We can convert the whole string to uppercase or lowercase, or we can convert it to initial caps, where the first letter of each word is uppercase and the rest of the word is lowercase.
Continue readingPrepared statements are a feature in PostgreSQL that can improve performance and security when executing repetitive SQL queries.
By creating a prepared statement, the server can parse and plan the query once and reuse it multiple times, leading to faster execution. Prepared statements also help in preventing SQL injection attacks by safely handling user input.
In this article, we’ll explore prepared statements in PostgreSQL, discuss how to create and use them, and cover other important considerations such as query planning and cleaning up statements.
Continue readingAdding indexes to a table is a well established practice for speeding up regular queries in relational databases such as PostgreSQL. While they can do wonders for query performance, indexes can also take up a lot of disk space.
Today we’re going to be looking at a particular type of index that can help reduce the impact on disk space, and query performance – the partial index.
Continue reading