When using aggregation functions in PostgreSQL, we have the option of including a FILTER clause. This clause allows us to narrow down – or filter – the rows that are fed to the aggregate function.
This can be be a handy way to provide functionality that we might otherwise need to use a CASE statement or perhaps a CTE.
In this article, we’ll take a look at PostgreSQL’s FILTER clause, and see how it can simplify our SQL queries by replacing CASE statements with more concise code.
When attempting to truncate a table in PostgreSQL, you might encounter the error “cannot truncate a table referenced in a foreign key constraint“. This is the default behaviour for the TRUNCATE statement whenever you try to truncate a table that is referenced by another table through a foreign key relationship.
If you want to truncate both tables, you can use the CASCADE option of the TRUNCATE statement. Alternatively, you could truncate both tables at the same time.
When we update an array in PostgreSQL, we can update one specific element or we can update a whole slice (a range of elements).
To update a slice, we use a syntax that references the lower and upper bounds of the slice, separated by a colon. We also provide the new values that will be assigned to each element within that slice.
Some RDBMSs support an enum type, which comprise a static, ordered set of values. Also known as enumerated types, enum types can be handy for making a column accept just a small set of values, such as days in the week, predefined clothing sizes, or any number of other preset values.
In PostgreSQL, if we want to create a table that uses an enum type, we need to create the enum type first, then apply it against the table. This is a bit different to other RDBMSs such as MySQL, where we don’t need to create the enum type first.
In PostgreSQL, a composite value is stored as a comma separated list of values, surrounded by parentheses.
When we have a table that contains composite values, we can extract individual fields by using dot notation to refer to the specific field that we want to extract.
In SQL (and computer science in general), a Boolean expression is a logical statement that evaluates to either true or false. Some RDBMSs provide a boolean data type that can store values that are either true or false (or unknown). PostgreSQL is one such RDBMS.
In PostgreSQL, the Boolean type is called boolean and it uses 1 byte.
When we use the format() function in PostgreSQL, we can pass any number of strings to insert into the resulting formatted string in positions that are specified in the first argument. When we do this, we might typically pass the strings as separate arguments. But this isn’t the only way to do it.
We have the option of passing the strings as array elements. That is, we can pass an array to the function, and have it extract each array element as though it’s a separate argument to insert into the formatted string.
PostgreSQL provides us with the ability to create arrays, extract element values from them, and more. When it comes to selecting values, there’s a special syntax that we must use in order to get the exact element that we want to select.
Basically, the syntax involves the array or column name, followed by a pair of square brackets with the subscript of the element that we want to select.
PostgreSQL allows us to create columns that store arrays. That is, we can define the column as an array column so that we can insert arrays into the column. This enables us to work with the arrays in the columns without it being confused with plain text.
We have a couple of options for inserting arrays into columns. One is to use an array literal. Another is to use an array constructor. Examples of each of these options are below.
When it comes to prepending a values to arrays in PostgreSQL, we have a number of options available to us. Below are four methods we can use to prepend a value to an array in PostgreSQL.