PostgreSQL provides us with various string functions, including some that allow us to trim a given string on both sides or a given side.
Below are four functions that we can use to trim strings in PostgreSQL.
Continue readingCategory: PostgreSQL
How to Truncate Tables with Foreign Key Relationships in PostgreSQL
When working with PostgreSQL databases, you may sometimes need to clear out all the data from tables that have foreign key relationships. This process, known as truncation, can be tricky when dealing with interconnected tables.
By default, if we try to truncate a table that is referenced by another table via a foreign key constraint, we’ll get an error that looks something like this: “ERROR: cannot truncate a table referenced in a foreign key constraint“.
You may have encountered this before finding this article. However, all is not lost. Below are two options for overcoming this issue.
Continue reading4 Ways to Insert Composite Data in PostgreSQL
When we have a column that’s defined as a composite type in PostgreSQL, we have some options when it comes to inserting data. For example, we can explicitly specify each individual field of the composite type, or we can use a row constructor to insert all fields at once.
Below are four different options for inserting composite values into a column in PostgreSQL.
Continue readingConcatenate Array Elements into a String in PostgreSQL
You may be aware that PostgreSQL has a couple of functions that allow us to concatenate strings. In particular, the concat() function allows us to concatenate multiple strings into one string, and the concat_ws() function allows us to do the same, but to also specify a separator for the concatenated strings.
But did you know that we have the ability to pass an array to these functions?
Continue readingEnlarging an Array in PostgreSQL
In PostgreSQL it’s possible to make an array larger without providing any values. For example, we can take an array with three elements, and enlarge it so that it contains say, five elements, with the two extra elements being NULL.
We can also do it so that some of the extra elements are non-NULL and others are NULL.
Understanding PostgreSQL’s FILTER Clause
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.
Fix Error “cannot truncate a table referenced in a foreign key constraint” in PostgreSQL
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.
How to Update an Array Slice in PostgreSQL
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.
Continue readingCreate an ENUM Type in PostgreSQL
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.
How to Extract Fields from Composite Values in PostgreSQL
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.
Continue reading