PostgreSQL – Append Suffixes to Column Based on Condition

pattern matchingpostgresql

I'd like to know if there is a better solution than the following to construct a new column (place_type_new) by adding a suffix to the textual information of an existing column (place_type) based on the value of a third column (the number of inhabitant in the case shown here):

  UPDATE places_table SET place_type_new = CASE
      WHEN inhabitant = 0 AND place_type LIKE 'Village' THEN 'Village_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'Village' THEN 'Village_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'Village' THEN 'Village_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'Village' THEN 'Village_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'Village' THEN 'Village_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'Village' THEN 'Village_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'Village' THEN 'Village_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'Village' THEN 'Village_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'Village' THEN 'Village_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'VillagePart' THEN 'VillagePart_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXXL'
      ELSE place_type
  END;

Here Village, VillagePart, Neighborhood and NeighborhoodPart are part of a larger set of places (for examples there are also Town, City and so on) and I only want this rule to apply for these 4 elements, hence my LIKE statements everywhere.

But I find this horribly ugly, but it's perfectly working. The suffix to append to the existing field values in the places_table in order to build the new place_table_new one, always matches the same number in the column inhabitant according to this pattern:

0     : XXXS
10    :  XXS
20    :   XS
100   :    S
2000  :    M
5000  :    L
10000 :   XL
20000 :  XXL
50000 : XXXL

Would it be better to build an intermediary table with this mapping in order to achieve this?

I'm using PostgreSQL 12 on Ubuntu 18.04. I also narrowed down the problem here to something as simple as I can, hopefully without forgetting something important.

Best Answer

you can reduce it to the following

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)

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;

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

db<>fiddle here

Related Question