If you’re getting an error that reads “source array too small” when updating an array in PostgreSQL, it’s probably because you’re passing a slice that’s smaller than your specified range. For example, if you specify a range that covers four elements but provide an array that contains just three elements, then you’d get this error.
To fix the error, be sure to provide an an array that contains at least as many elements as are specified in the range.
Continue readingIn PostgreSQL, the quote_nullable() function returns a given string suitably quoted to be used as a string literal in a SQL statement string. It returns the actual string NULL if the argument is null.
Any embedded single-quotes and backslashes are properly doubled.
Continue readingIf you’re getting an error that reads “function array_shuffle(integer, integer) does not exist” when using the array_shuffle() function in PostgreSQL, it’s probably because the argument you’re passing to the function is not an array.
Actually, the error message implies that you’re passing multiple integer values (two in this case, but it could be more), separated by a comma. Perhaps you’re trying to pass an array of integers, but you haven’t provided them in an array. Or perhaps you’re passing the wrong column or variable. Either way, the argument for this function must be an array.
To fix this error, replace the integer values with a single array value when calling the array_shuffle() function.
When we create a scheduled event in MySQL, we have the option of adding a comment to the event. This comment can be a string of up to 64 characters. We have a couple of options when it comes to viewing the comment of an existing event.
Continue readingIn PostgreSQL, we can use the overlay() function to replace a substring within a string. The function allows us to be very specific with regards to where in the string to start the operation and how much of it should be replaced.
PostgreSQL has a couple of functions that enable us to get the position or positions of a given value in an array. One of them – array_position() – returns the position of just the first occurrence (as an integer), while the other function – array_positions() – returns the position of all occurrences (as an array).
So these functions are aptly named – one is singular and the other is plural.
But this isn’t the only difference. The array_position() function allows us to specify a start position, while array_positions() doesn’t.
If you’re getting a PostgreSQL error that reads “op ANY/ALL (array) requires array on right side” it’s probably because you’re passing a non array to the ANY() or ALL() construct.
These constructs require an array to be included in the parentheses, and so passing a non array returns an error.
To fix this issue, be sure to pass an array when using these functions.
Continue readingIn PostgreSQL, the ltrim() function trims the left side of a string. We can trim blank spaces or we can specify certain characters to trim.
It removes the longest string containing only characters from the ones we specify. If we don’t specify any characters, then it removes blank spaces.
Continue readingIn PostgreSQL, we can use the rtrim() function to trim the right side of a string. We can trim blank spaces or we can specify certain characters to trim.
It removes the longest string containing only characters from the ones we specify. If we don’t specify any characters, then it removes blank spaces.
Continue readingIn PostgreSQL, the trim() function can be used to trim both sides, or a specified side, of a string. We can trim blank spaces or we can specify certain characters to trim.
It removes the longest string containing only characters from the ones we specify. If we don’t specify any characters, then it removes blank spaces.
Continue reading