Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE places_table(inhabitant INT,place_type VARCHAR(50),place_type_new VARCHAR(50) )
iNSERT INTO places_table vaLUES (0,'Village',NULL)
,(50,'NeighborhoodPart',NULL),(10000,'Neighborhood',NULL),(10000,'city',NULL)
4 rows affected

UPDATE places_table SET place_type_new = 
 CASE wHEN place_type IN ('Village','VillagePart','Neighborhood','NeighborhoodPart') THEN
   CASE
      WHEN inhabitant = 0  THEN CONCAT(place_type,'_XXXS')
      WHEN inhabitant <= 10  THEN CONCAT(place_type,'_XXS')
      WHEN inhabitant <= 20  THEN CONCAT(place_type,'_XS')
      WHEN inhabitant <= 100 THEN CONCAT(place_type,'_S')
      WHEN inhabitant <= 2000  THEN CONCAT(place_type,'_M')
      WHEN inhabitant <= 5000  THEN CONCAT(place_type,'_L')
      WHEN inhabitant <= 10000 THEN CONCAT(place_type,'_XL')
      WHEN inhabitant <= 20000  THEN CONCAT(place_type,'_XXL')
      WHEN inhabitant <= 50000  THEN CONCAT(place_type,'_XXXL')
     ELSE place_type
  END
ELSE place_type
END;
4 rows affected

seLECT * FROM places_table


inhabitant
place_type
place_type_new


0
Village
Village_XXXS

50
NeighborhoodPart
NeighborhoodPart_S

10000
Neighborhood
Neighborhood_XL

10000
city
city

inhabitant	place_type	place_type_new
0	Village	Village_XXXS
50	NeighborhoodPart	NeighborhoodPart_S
10000	Neighborhood	Neighborhood_XL
10000	city	city