Temporary Tables

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

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.

Documentation

• 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

begin;
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;
commit;