Re: extracting location info from string

On Mon, 23 May 2011 00:25:16 -0400
Lew <noone(at)lewscanon(dot)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