1

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.

1 Answer 1

1

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

4
  • Thank you! You probably forgot to add something like AND (place_type in ('Village','Neighborhood','NeighborhoodPart')) after the numbers in all your WHEN statements. This is to reproduce the LIKE statements of my original queries, because, I didn't show it, but there are some other types of places for which I don't want to compute this new field, so keeping only the old value without any suffix is OK.
    – s.k
    Apr 3, 2021 at 19:36
  • you didn't write that in your question
    – nbk
    Apr 3, 2021 at 19:47
  • I fixed this by explaining it with more details ;-)
    – s.k
    Apr 3, 2021 at 19:50
  • 1
    i changed the answer by adding the further case with all 4 places and i changed ithe inhabitants to <= which would make more sense, as the number shouldn't be that fix
    – nbk
    Apr 3, 2021 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.