Temporary Tables

:heavy_exclamation_mark: This post is older than a year. Consider some information might not be accurate anymore. :heavy_exclamation_mark:

Temporary tables exist in a special schema. If specified in the create table statement, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction. .. Any indexes created on a temporary table are automatically temporary as well.


  • Parsing street name with number and separate them in different columns.
  • Parsing with regexp_matches for street names in the German language.
    --drop table tempstreet;
    create temporary table tempstreet(id integer, data text[2]);
    insert into tempstreet 
         select customer_id, 
                regexp_matches(street, (^[A-ZÄÖÜa-zäöüß[A-ZÄÖÜa-zäöüß(-{}| )]+[.| |-])[ ]?([1-9][0-9]{0,3}[ ]?[a-z]?)')
           from crm.addresses;

Update the data from the temporary table to update the table crm.addresses

update crm.addresses set street=data[1] from tempstreet where tempstreet.id = customer_id;
update crm.addresses set hausnr=data[2] from tempstreet where tempstreet.id = customer_id;
Please remember the terms for blog comments.