Postgres Return multiple row column values as string

Question

I have a table called servers with columns server and site as laid out below:

table servers:
server       site
servern1    .biz.net
servern2    .biz.net
servern3    .biz.net

I'm trying to get an output as a string in one line as shown below

servern1.biz.net:servern1;servern2.biz.net:servern2;servern3.biz.net:servern3

and not had much luck as my attempt below shows

postgres=# select array_to_string(array_agg(server),':')||site||':'||server as vals from servers group by server,site;

          vals           
-------------------------
 servern2.biz.com:servern2
 servern3.biz.com:servern3
 servern1.biz.com:servern1
(3 rows)

Any help/advice is appreciated! Thanks

user330315 · Accepted Answer · 2020-06-21 17:58:07Z

1

The following should do it:

select string_agg(server||':'||site, ';') as vals
from servers;

If either column could be empty or null, concat_ws() might be a better choice:

select string_agg(concat_ws(':', server, site), ';') as vals
from servers;

answered Jun 21, 2020 at 17:58

user330315

Sign up to request clarification or add additional context in comments.

Comments

ribbit · Accepted Answer · 2020-06-21 23:27:05Z

1

That's ace, thank you @a_horse_with_no_name

I've just tweaked a little for the desired result as shown below

select string_agg(concat_ws('', server, site,':',server), ';') as vals from servers;

                                  vals                                   
-------------------------------------------------------------------------
servern1.biz.net:servern1;servern2.biz.net:servern2;servern3.biz.net:servern3
(1 row)

answered Jun 21, 2020 at 23:27

ribbit

793 silver badges16 bronze badges

Collectives™ on Stack Overflow

Postgres Return multiple row column values as string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related