All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 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_tableinhabitant | place_type | place_type_new ---------: | :--------------- | :----------------- 0 | Village | Village_XXXS 50 | NeighborhoodPart | NeighborhoodPart_S 10000 | Neighborhood | Neighborhood_XL 10000 | city | city
db<>fiddle here
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0