Postgres ARRAY_TO_STRING() function is explained with practical examples in this write-up. SELECT testing ('arg1', 'arg2', 'arg3') a function that accepts an array as parameter: CREATE FUNCTION testing (firstname text ). There are two ways: a variadic function: CREATE FUNCTION testing (VARIADIC firstname text ). The return type of the ARRAY_TO_STRING() function is TEXT. If you want to pass several values, you have to define the function to accept several values. The ARRAY_TO_STRING() function converts the given array into strings and concatenates the strings using a delimiter/separator. PostgreSQL offers a built-in array function named ARRAY_TO_STRING() that accepts three arguments: an array, a delimiter/separator, and a text to replace the null values. The output clarifies that the input array has been converted into a string. “-” is passed as the third parameter that will replace the NULL value(if any) with “-”. A colon is passed as a separator/delimiter. An array-type column is passed as the first argument to the ARRAY_TO_STRING() function. Let’s use the ARRAY_TO_STRING() function on “st_name” array to convert it into a string: SELECT ARRAY_TO_STRING(st_name, ':', '-') We have created a table named “st_information” that contains the following data: SELECT * FROM st_information The output shows that the NULL values have been replaced with the specified null_text, i.e., “0”.Įxample 3: How to Use ARRAY_TO_STRING() Function on Table’s Data in Postgres? Consequently, it will replace the NULL value(if any) with 0: SELECT ARRAY_TO_STRING( We will pass “0” as the third parameter to the ARRAY_TO_STRING() function. In the below-given input array, there are some NULL values. The output snippet proves that the given array has been converted into a string, and a comma is concatenated with each array element.Įxample 2: How Does the ARRAY_TO_STRING() Function Deals With the Null Values in Postgres? And “0” is passed as the third parameter that will replace the NULL value(if any) with the 0. An array is passed as the first argument. Let’s learn the working of the ARRAY_TO_STRING() function using the below code: SELECT ARRAY_TO_STRING(ĪRRAY, ',', '0' The return type of the ARRAY_TO_STRING() function will be TEXT.Įxample 1: How to Use ARRAY_TO_STRING() Function in Postgres? Note: Postgres uses 1-based numbering for array indices, so the first array element has an index of 1 and not 0: deplist1. “text” is an optional parameter that replaces the NULL values with the specified text. “sep” represents a separator/delimiter that will be used between the strings. “arr” represents an array to be converted to strings. In Postgres, the below-mentioned syntax is used for the ARRAY_TO_STRING() function: ARRAY_TO_STRING(arr, sep) How to Use ARRAY_TO_STRING() Function in Postgres? This write-up will teach you how to use the ARRAY_TO_STRING() function in Postgres via suitable examples. The separator can be any value, such as white space, comma, semi-colon, etc. PostgreSQL offers a built-in array function named ARRAY_TO_STRING() that accepts an array, converts it into strings, and concatenates the strings using a delimiter/separator.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |