Value that matches every possible value (except null of course)?

Question

Is there any trick in postgresql to make a value match every possible value, a kind of "Catch all" value, an anti-NULL ?

Right now, my best idea is to choose a "catchall" keyword and force a match in my queries.

WITH cities AS (SELECT * FROM (VALUES('USA','New York'),
                                     ('USA','San Francisco'),
                                     ('Canada','Toronto'),
                                     ('Canada','Quebec')
                               )x(country,city)),
     zones  AS (SELECT * FROM (VALUES('USA East','USA','New York'),
                                     ('USA West','USA','San Francisco'),
                                     ('Canada','Canada','catchall')
                               )x(zone,country,city))
SELECT z.zone, c.country, c.city
FROM cities c,zones z
WHERE c.country=z.country
  AND z.city IN (c.city,'catchall');

   zone   | country |     city
----------+---------+---------------
 USA East | USA     | New York
 USA West | USA     | San Francisco
 Canada   | Canada  | Toronto
 Canada   | Canada  | Quebec

If a new canadian town was inserted in the "cities" table, the "zones" table would automatically recognize it as part of the 'Canada' zone. The above query satisfies the functionality I'm looking for, but it feels awkward and prone to errors if repeated multiple times in a wide database.

Is this the proper way to do it, is there a better way, or am I asking the wrong question ?

Thanks a lot for your answers!

Answer 1

Personally, I think that NULL makes a better choice for this:

select z.zone, c.country, c.city
from cities c join
     zones z
     on c.country = z.country and
        (c.city = z.city or z.city is null);

or even:

select z.zone, c.country, c.city
from cities c join
     zones z
     on c.country = z.country and
        c.city = coalesce(z.city, c.city);

As per Denis, Postgres seems to be smart enough to use an index on the first query for both country and city.

You could also do a two part join, if you have indexes on both zone(country) and on zone(country, city), you could do a two part join:

select coalesce(zcc.zone, zc.zone) as zone, c.country, c.city
from cities c join
     zones zcc
     on c.country = z.country and
        c.city = z.city join
     zones zc
     on c.country = z.country and
        zc.city is null;

Although a bit more complicated, both joins should be able to use appropriate indexes.