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
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
