Date: Mon, 23 May 2011 16:33:41 +0100
From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Re: extracting location info from string
Message-ID: <20110523163341.56edb1aa@dick.coachhouse>
In-Reply-To: <ircnj2$cpt$1@news.albasani.net>
References: <20110522210526.0f8b66fa@dick.coachhouse>
	<20110522230041.28d1bc35@dick.coachhouse>
	<BANLkTikGzsrvXprLXeN3TxZwA=M8fzkn=g@mail.gmail.com>
	<4DD9BB87.7070000@postnewspapers.com.au>
	<ircnj2$cpt$1@news.albasani.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Mon, 23 May 2011 00:25:16 -0400
Lew <noone@lewscanon.com> wrote:

>That isn't a table structure, that's a freeform text structure.  You
>didn't state your question, Tarlika, but your database structure is
>terrible.  For example, "region" and "country" should be different
>columns. 

I presume you are referring to my original post:
CREATE TABLE person
(
  id integer NOT NULL,
  "name" character varying(256) NOT NULL,
  "location" character varying(256),
  CONSTRAINT person_pkey PRIMARY KEY (id)
);

Sorry, this was just a TEMPORARY table I created for quick analysis of
my CSV data (now renamed to temp_person).


The target table is:
CREATE TABLE person
(
  id integer NOT NULL,
  "name" character varying(100) NOT NULL,
  country character varying(3),
  county character varying(3),
  town character varying(50),
  CONSTRAINT trainer_pkey PRIMARY KEY (id),
  CONSTRAINT country_person_fk FOREIGN KEY (country)
      REFERENCES country (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT county_person_fk FOREIGN KEY (country, county)
      REFERENCES county (country, code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
);


-- 

Best Regards,
Tarlika Elisabeth Schmitz