Does PostgreSQL function concat_ws works as expected?

Question

You find the documentation of the concat_ws PostgreSQL function here.

It says this request concat_ws(',', 'abcde', 2, NULL, 22) results in this reply abcde,2,22.

This example does not proves the documentation.

poi=> select * from (select p.name as name_org,                      CASE
                     WHEN (p.amenity = 'vending_machine' AND p.vending = 'public_transport_tickets') THEN concat(',', p.name, p.operator, 'Ticketautomat')
                     WHEN p."tickets:public_transport" = 'only'  THEN concat(',', p.name, p.operator, 'Kundencenter')
                     WHEN p."tickets:public_transport" = 'yes' THEN  concat(',', p.name, p.operator, 'Vorverkaufsstelle')
                     ELSE p.name
                     END AS name,               CASE
                WHEN p."tickets:public_transport" = 'only' THEN
                5 --- customer center
                WHEN p."tickets:public_transport" = 'yes' THEN
                6 --- presale shops
                WHEN (p.amenity = 'vending_machine' AND p.vending = 'public_transport_tickets' AND (p."payment:bob" IS NULL OR p."payment:bob" = 'no')) THEN
                18 --- ticket machine
                WHEN (p.amenity = 'vending_machine' AND p.vending = 'public_transport_tickets' AND p."payment:bob" = 'yes') THEN
                21 --- ticket machine BOB
                ELSE 999
                END AS kategorie from public.planet_osm_point as p where p.amenity = 'vending_machine' AND p.vending = 'public_transport_tickets' AND p."payment:bob"= 'yes' AND p.proposed is null) AS foo where foo.name IS NOT NULL  AND foo.kategorie <> 999;
 name_org |                                 name                                 | kategorie 
----------+----------------------------------------------------------------------+-----------
          | ,NordWestBahn GmbHTicketautomat                                      |        21
          | ,NordWestBahn GmbHTicketautomat                                      |        21
          | ,NordWestBahn GmbHTicketautomat                                      |        21
          | ,NordWestBahn GmbHTicketautomat                                      |        21
          | ,DB Vertrieb GmbHTicketautomat                                       |        21
          | ,DB Vertrieb GmbHTicketautomat                                       |        21
          | ,NordWestBahn GmbHTicketautomat                                      |        21
          | ,NordWestBahn GmbHTicketautomat                                      |        21
          | ,NordWestBahn GmbHTicketautomat                                      |        21
          | ,NordWestBahn GmbHTicketautomat                                      |        21
          | ,NordWestBahnTicketautomat                                           |        21
          | ,Nordwestbahn GmbHTicketautomat                                      |        21
          | ,DB Vertrieb GmbHTicketautomat                                       |        21
          | ,Ticketautomat                                                       |        21
          | ,Eisenbahnen und Verkehrsbetriebe Elbe-Weser GmbH (EVB)Ticketautomat |        21
          | ,Nordwestbahn GmbHTicketautomat                                      |        21

Here you find some Nodes from the OSM database (imported into PostgreSQL using osm2pgsql) that have typically the tag name not set and the tag operator sometimes not set.

That is why I decided to test the concat_ws PostgreSQL function to build a comprehensive name string using the name and operator string and a use case specific prefix.

In case the name and operator tag is available I expect a reply like name,operator,Ticketautomat. I have no example for this case.

In case the operator tag is available I expect a reply like operator,Ticketautomat. In the above example I get a reply like ,Nordwestbahn GmbHTicketautomat. From my perspective is the first comma too much and a comma before Ticketautomat missing.

In case neither name nor operator tag is available I expect a reply like Ticketautomat. In the above example I get a reply like ,Ticketautomat. From my perspective is the first comma too much.

I expected, that NULL columns are ignored and the separator to be applied otherwise. What am I doing wrong? Have I misinterpreted the documentation?

Answer 1

Many thanks to @geozelot who pointed me to my typo. When I substitute concat(' with concat_ws(' I get this query

select * from (select p.name as name_org,                      CASE
                     WHEN (p.amenity = 'vending_machine' AND p.vending = 'public_transport_tickets') THEN concat_ws(',', p.name, p.operator, 'Ticketautomat')
                     WHEN p."tickets:public_transport" = 'only'  THEN concat_ws(',', p.name, p.operator, 'Kundencenter')
                     WHEN p."tickets:public_transport" = 'yes' THEN  concat_ws(',', p.name, p.operator, 'Vorverkaufsstelle')
                     ELSE p.name
                     END AS name,               CASE
                WHEN p."tickets:public_transport" = 'only' THEN
                5 --- customer center
                WHEN p."tickets:public_transport" = 'yes' THEN
                6 --- presale shops
                WHEN (p.amenity = 'vending_machine' AND p.vending = 'public_transport_tickets' AND (p."payment:bob" IS NULL OR p."payment:bob" = 'no')) THEN
                18 --- ticket machine
                WHEN (p.amenity = 'vending_machine' AND p.vending = 'public_transport_tickets' AND p."payment:bob" = 'yes') THEN
                21 --- ticket machine BOB
                ELSE 999
                END AS kategorie from public.planet_osm_point as p where p.amenity = 'vending_machine' AND p.vending = 'public_transport_tickets' AND p."payment:bob"= 'yes' AND p.proposed is null) AS foo where foo.name IS NOT NULL  AND foo.kategorie <> 999;

and this result, that fullfills my expectation.

 name_org |                                 name                                 | kategorie 
----------+----------------------------------------------------------------------+-----------
          | Eisenbahnen und Verkehrsbetriebe Elbe-Weser GmbH (EVB),Ticketautomat |        21
          | Eisenbahnen und Verkehrsbetriebe Elbe-Weser,Ticketautomat            |        21
          | Eisenbahnen und Verkehrsbetriebe Elbe-Weser,Ticketautomat            |        21
          | Eisenbahnen und Verkehrsbetriebe Elbe-Weser,Ticketautomat            |        21
          | Metronom,Ticketautomat                                               |        21
          | Metronom Eisenbahngesellschaft mbH,Ticketautomat                     |        21
          | Metronom Eisenbahngesellschaft mbH,Ticketautomat                     |        21
          | NordWestBahn GmbH,Ticketautomat                                      |        21
          | Ticketautomat                                                        |        21

Cheers!