If you’re getting SQL Server error 2788 that reads “Synonyms are invalid in a schemabound object or a constraint expression” it seems that you’re trying (whether intentionally or not) to create a schemabound object that includes a synonym in its definition, or a constraint with a synonym in its expression.
Continue readingTag: how to
Using UPDATE with a Subquery in SQL
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.
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.
Continue readingHow 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.
Continue readingWhen LPAD() or RPAD() Don’t Seem to Work in PostgreSQL
If you’re trying to use either lpad() or rpad() to pad a string in PostgreSQL but it’s just not working, maybe it’s because of the reasons listed below.
Fortunately this issue has a simple fix, and it can be resolved within seconds.
Continue readingHow 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.
Continue reading2 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.
Continue readingHow 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.
Continue readingHow 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.