We have several options when it comes to appending elements to arrays in PostgreSQL. We can use an operator to concatenate the value to the array or we can use a function to do the job.
Below are four ways to append elements to arrays in PostgreSQL.
How ARRAY_FILL() Works in PostgreSQL
In PostgreSQL we can use the array_fill() function to create an array filled multiple instances of a given value.
We pass the value to populate as the first argument, followed by the length of the desired array. The resulting array replicates the first argument as specified by the length in the second argument.
We also have the option of passing a third argument to specify the lower bound values of each dimension of the array.
Using ARRAY_TO_STRING() in PostgreSQL
In PostgreSQL we can use the array_to_string() function to convert an array to a string. It converts each array element to its text representation (if needed), and then concatenates all elements using the given delimiter.
Any null values are omitted from the result, but we do have the option of replacing any null values with a given text value.
An Overview of UNNEST() in PostgreSQL
In PostgreSQL, we can use the unnest() function to expand an array into a set of rows. We pass the array as an argument, and the function returns each element on a separate row.
We can use the function on multi dimensional arrays, and it’s also possible to use it to unnest multiple arrays by including it in the FROM clause of a query.
Difference Between QUOTE_LITERAL() and QUOTE_NULLABLE() in PostgreSQL
On the surface, PostgreSQL’s quote_literal() and quote_nullable() functions appear to do the same thing. But there is a difference.
It all comes down to how they deal with null arguments; quote_literal() returns null while quote_nullable() returns the string NULL.
Fix Error “time field value out of range” when using make_time() in PostgreSQL
If you’re getting an error that reads ‘time field value out of range‘ in PostgreSQL while using the make_time() function, it’s probably because one or more of the time parts you’re providing is out of the accepted range for that time part.
To fix this issue, be sure that each time part you provide is within the valid range for that time part.
Understanding the NORMALIZE() Function in PostgreSQL
PostgreSQL has a normalize() function that converts a string to the specified Unicode normalization form.
The function can only be used when the server encoding is UTF8.
How PostgreSQL’s CHARACTER_LENGTH() Function Works
In PostgreSQL, we can use the character_length() function to return the number of characters in a given string.
It accepts one argument; the string for which to return the length.
The character_length() function is a synonym for the char_length() function and so both do the same thing. We can also use the length() function to get the same result.
About PostgreSQL’s LENGTH() Function
In PostgreSQL the length() function returns the number of characters in a given string.
We pass the string as an argument and the function returns the number of characters as an integer.
A Quick Look at CHAR_LENGTH() in PostgreSQL
In PostgreSQL, the char_length() function returns the number of characters in a string. We pass the string as an argument and the function returns the number of characters in that string.
We can also use character_length() and length() to get the same result.