CODEWITHSUNDEEP
sqlarrayspostgresql
ryh12
ryh12

Reputation: 367

Get array elements space separated instead of comma separated

I have a table "Health" in PostgreSQL with two columns

Health_id   integer
health_data double precision[]

Example data:

Health_id  health_data
1          {1,2.1,3.2,8.1,9.0}

I want to select the array health_data from Health where health_id = ?, but I am getting the array with commas: 

{1,2.1,3.2,8.1,9.0}

How can I get the array with spaces instead of commas, Like this:

{1 2.1 3.2 8.1 9.0}

Upvotes: 1

Views: 1030

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

The default text representation of arrays has commas to separate elements.
Use array_to_string() to produce the text representation you desire (actual type text):

SELECT array_to_string(health_data, ' ') AS health_data_with_spaces
FROM   "Health";

If you want the surrounding {}, add them manually:

SELECT '{' || array_to_string(health_data, ' ') || '}' AS health_data_with_spaces
FROM   "Health";

Upvotes: 2

BillRobertson42
BillRobertson42

Reputation: 12883

array_to_string should do it.

select '{' || array_to_string(array[1,2.1,3.2,8.1,9.0], ' ') || '}';
      ?column?       
---------------------
 {1 2.1 3.2 8.1 9.0}

https://www.postgresql.org/docs/9.5/static/functions-array.html

Upvotes: 0

Related Questions