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 readingCategory: DBMS
Database Management Systems
A Quick Look at the FACTORIAL() Function in PostgreSQL
In PostgreSQL, factorial() is a mathematical function that returns the factorial of a given integer. We pass the integer (it accepts bigint), and it returns the factorial as a numeric value.
In mathematics, the factorial is the product of all positive integers less than or equal to a given positive integer. It’s denoted by that integer and an exclamation point.
Continue readingConcatenate 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 readingUnderstanding Domains in PostgreSQL
PostgreSQL offers a handy feature called domains that can greatly enhance data integrity and simplify database design. In this article, we’ll explore what domains are, how they work, and when to use them in our database projects.
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 readingA Quick Look at the MD5() Function in PostgreSQL
In PostgreSQL, the md5() function computes the MD5 hash of the specific text, and outputs the result in hexadecimal.
We pass the text as an argument when calling the function.
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 reading