Date: Sun, 22 May 2011 21:05:26 +0100
From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: extracting location info from string
Message-ID: <20110522210526.0f8b66fa@dick.coachhouse>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

A column contains location information, which may contain any of the
following:

1) null
2) country name (e.g. "France")
3) city name, region name (e.g. "Bonn, Nordrhein-Westfalen")
4) city name, Rg. region name (e.g. "Frankfurt, Rg. Hessen")
5) city name, Rg region name (e.g. "Frankfurt, Rg Hessen")

Using the tables below, I would like to derive COUNTRY.ID, REGION.CODE,
city name.


=====
CREATE TABLE person
(
  id integer NOT NULL,
  "name" character varying(256) NOT NULL,
  "location" character varying(256),
  CONSTRAINT person_pkey PRIMARY KEY (id)
);

CREATE TABLE country
(
  id character varying(3) NOT NULL, -- alpha-3 code
  "name" character varying(50) NOT NULL,
  CONSTRAINT country_pkey PRIMARY KEY (id)
);

CREATE TABLE region
(
  country character varying(3) NOT NULL,
  code character varying(3) NOT NULL,
 "name" character varying(50) NOT NULL,
  CONSTRAINT region_pkey PRIMARY KEY (country, code),
  CONSTRAINT country_region_fk FOREIGN KEY (country)
      REFERENCES country (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)


=====
System: PostgreSQL 8.4


-- 

Best Regards,
Tarlika Elisabeth Schmitz