In 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 readingIf you’re getting an error that reads ‘date field value out of range‘ in PostgreSQL while using the make_date() function, it’s probably because one or more of the date parts you’re providing is out of range for that date part.
It’s possible that you’ve got the month and day in the wrong order.
To fix this issue, be sure that each date part you provide is within the valid range for that date part. It may be that all you need to do is switch the day and the month around.
Continue readingWe can use the array_remove() function in PostgreSQL to remove elements from an array.
We pass two arguments when calling the function; the first is the array and the second argument is the element to remove. All occurrences of the specified element are removed.
The function returns an identical array, but without the specified element/s.
Continue readingBy default, PostgreSQL arrays are one-based. That means that the numbering starts at subscript 1 and increments with each element in the array. However, we can change this so that the array starts at a different subscript.
Continue readingIn PostgreSQL, the octet_length() function returns the number of bytes in a given string. We pass the string as an argument and the function returns the number of bytes in that string.
PostgreSQL has a bit_length() function that returns the number of bits in a given string. We pass the string as an argument and the function returns the number of bits in that string.
If you’re getting a PostgreSQL error that reads “function generate_subscripts(numeric, integer) does not exist“, it’s probably because your first argument is not an array. This specific error message implies that the first argument is a numeric value, but we’d get a similar error when passing an integer.
The first argument for this function must be an array.
To fix this error, be sure that your first argument to generate_subscripts() is an array.
In PostgreSQL, we can use the array_cat() function to concatenate two arrays.
We pass both arrays as arguments when we call the function.
Continue readingIf you’re getting a PostgreSQL error that reads “searching for elements in multidimensional arrays is not supported“, it’s probably because you’re trying to perform an operation against a multi dimensional array when that operation only supports one dimensional arrays.
To fix this issue, perform the operation against a one dimensional array instead. You may be able to do this by applying the unnest() function against the multi dimensional array.
PostgreSQL has a function called btrim() that we can use to trim both sides 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 from both sides.
We provide the string as the first argument, and the (optional) characters as the second.
Continue reading