Ever found yourself needing to update a bunch of rows in your database, but the condition for the update depends on data from another table? That’s where UPDATE with a subquery comes in handy. Let’s break it down.
how to
Concatenate Arrays in PostgreSQL with the || Operator
In PostgreSQL, we can use the || operator to concatenate two arrays. We can also use it to append or prepend a (compatible) non-array value to an array.
Fix Error “Truncate failed on table … because it is not a supported operation on system-versioned tables.” in SQL Server
If you’re getting an error that reads something like “Truncate failed on table ‘test.dbo.ProductInventory’ because it is not a supported operation on system-versioned tables.” in SQL Server, it’s probably because you’re trying to truncate a system-versioned temporal table.
In SQL Server, if a table is defined as a temporal table, we can’t truncate it while it’s using system-versioning.
How to Update an Array in PostgreSQL
PostgreSQL allows us to create arrays and store them in the database as an array type. We can then go back later and update those arrays as required.
We can either replace the whole array, append or prepend elements, or update individual elements within the array. When we update individual elements, we can update one element or a whole slice. Below are examples of updating an array in PostgreSQL.
How to DROP Tables with Foreign Key Relationships in PostgreSQL
Nothing pulls me out of the “zone” quicker than attempting to drop a table, only to be told “Nope, that table has a dependency!”.
Granted, this usually only happens in my development environments, as I rarely have any need to drop tables in a production environment.
And that’s probably why it “pulls me out of the zone”. I suddenly have to stop and start thinking about what tables contain foreign keys to the one I’m trying to drop. In dev environments, we’re often trying things out and so it’s not out of the question that we might inadvertently try to drop a table without realising it has dependent objects.
Fortunately, when we’re working with PostgreSQL, we have a quick and easy method to overcome this barrier.
2 Functions that Return the Position of a Substring within a String in PostgreSQL
PostgreSQL sometimes provides us with multiple functions that basically do the same thing. Such is the case with the following two functions that return the position of a given substring within a string.
How to TRUNCATE a Temporal Table in SQL Server
Temporal tables in SQL Server provide a powerful way to track historical data changes. However, when it comes to clearing out data from these tables, the standard TRUNCATE statement doesn’t work the same as it does on normal (non-temporal) tables.
This article will guide you through the process of truncating a temporal table while maintaining its integrity.
How to Specify your own Subscript Range when Creating an Array in PostgreSQL
By default, PostgreSQL arrays are one-based. This means that we need to use 1 if we want to reference the first element in the array, 2 for the second, and so on.
But we also have the option of specifying our own subscript range for an an array. For example we could create a zero-based array, a ten-based array, or even a negative value such as a negative ten-based array.
We can do this by using subscripted assignment to specify the actual subscript range for the array. Basically, we prefix the array with the subscript range, enclosed in square brackets, and an equals sign (=) between it and the array.
Understanding the SQL Subquery
One of the things I love about SQL is just how easy it is to write a simple query that returns a meaningful result set, without having to a whole bunch of complex code. And I imagine most beginners are relieved when they discover this too.
However, while basic queries can go a long way, there’s a whole world of advanced techniques that can take our SQL development to another level. Somewhere in that world, would be the SQL subquery.
In this article, we look at the SQL subquery. We’ll explore what subqueries are, how they work, and when to use them. We’ll also look at some simple examples to demonstrate their use.