CODEWITHSUNDEEP
arrayspostgresqlinsert
Guit
Guit

Reputation: 53

Postgres 8.4.3: How do convert names and string of names to normalized numerical ids using unnest

I have a table region_town_names with two character fields, one contains a region name, the other a comma separated list of towns

region  | towns
-------------------------------
regionA | townA, townB, townC    
regionB | townB, townD

I also have two tables (region_id and town_id) with numerical ids for each region/town

id  | name                              id  |  name
---------------                         ----------------
 1  | regionA                            1  |  townA
 2  | regionB                            2  |  townB
                                         3  |  townC
                                         4  |  townD

I'm now trying to fill a normalized table region_town_ids which should unnest the town lists and contain the ids of region and towns like this:

 region_id | town_id
 -------------------
    1      |    1
    1      |    2
    1      |    3
    2      |    2
    2      |    4

I could esaily get the names and insert them with

 insert into region_town_ids
   select region as region_id, unnest(string_to_array(towns,', ')) as town_id 
   from region_town_names;

but how can I lookup the ids of the names in the same statement and insert them instead of the names? Is that possible? Do I need a psql function? Thanks.

Upvotes: 3

Views: 107

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

SQL Fiddle

select
    rid.id region_id,
    tid.id town_id
from
    (
        select
            region region_name,
            unnest(string_to_array(towns,', ')) town_name
        from region_town_names
    ) rtn
    inner join
    region_id rid on rid.name = rtn.region_name 
    inner join
    town_id tid on tid.name = rtn.town_name

This answers your question but I suspect you are getting it wrong. Notice how the the town_id #2 belongs to two regions. Is it possible?

Also I think you could simplify the model eliminating the region_town_ids table and making the town_id table like this:

(id, region_id, name)

Upvotes: 1

Related Questions