SQL: How to replace values in a union of conflicting types

Question

I have a single table in Postgres (with PostGIS) in which I have a few thousand rows, each with the name(text), code(text), city(text) and geom(geometry) of a statistical area unit (like a county). The geometries tessellate perfectly.

I'm trying to write a single query that will select all the rows that meet some criteria, and aggregate the rest into a single row. For that single row, I'm not interested in the name and code, just the aggregated geom. E.g., something like this:

 code | name               |  geom
------+--------------------+---------
 102  | Central Coast      | geo...
 115  | Sydney - Baulkham  | geo...
 116  | Sydney - Blacktown | geo...
 117  | Sydney - City      | geo...
 118  | Sydney - Eastern   | geo...
 000  | Remaining Counties | geo... <---Second SELECT just to get this row

I used this answer to come up with the following:

SELECT code, name, ST_Force2D(geom) FROM mytable WHERE mytable.city = 'Greater Sydney' UNION 
SELECT 
  CASE WHEN count(code) > 0 THEN '0' ELSE 'x' END AS code, 
  CASE WHEN count(name) > 0 THEN 'Rest of Sydney' ELSE 'x' END AS name, 
  ST_Collect(geom) 
FROM mytable WHERE mytable.city <> 'Greater Sydney';

This seems like a really roundabout and unclear way of accomplishing something pretty simple. Is there a better way to do this?

Answer 1

You can hard-code what you want those cells to populate with. I believe in postgres you do this with ticks:

SELECT code, name, ST_Force2D(geom) 
FROM mytable 
WHERE mytable.city = 'Greater Sydney'

UNION 

SELECT '0', 'Remaining Countries', ST_Collect(geom) 
FROM mytable 
WHERE mytable.city <> 'Greater Sydney';

The answer you found compensates for zero values and replaces them with X. If you prefer to see Xs in that case, you can do it that way instead. Seems unnecessary to me.