Re: Modifying database schema without losing data

On 9/28/20 10:15 AM, Rich Shepard wrote:
> I've been developing a business tracking application for my own use and
> it's
> worked well up to now. But, I need to modify it by adding a table with
> attributes from two other tables. I've not drawn a E-R diagram so I show
> the
> two existing tables here:
>
> CREATE TABLE Organizations (
>   org_id serial PRIMARY KEY,
>   org_name varchar(64) DEFAULT '??' NOT NULL,
>   org_addr1 varchar(64),
>   org_addr2 varchar(64),
>   org_city varchar(16),
>   state_code char(2),
>   org_postcode varchar(10),
>   org_country char(2) DEFAULT 'US' NOT NULL,
>   main_phone varchar(16),
>   org_fax varchar(12),
>   org_url varchar(64),
>   industry varchar(24) DEFAULT 'Other' NOT NULL
>            REFERENCES industries(ind_name)
>            ON UPDATE CASCADE
>            ON DELETE RESTRICT,
>   status varchar(20) DEFAULT 'Opportunity' NOT NULL
>          REFERENCES statusTypes(stat_name)
>          ON UPDATE CASCADE
>          ON DELETE RESTRICT,
>   comment text );
>
> CREATE TABLE People (
>   person_id serial PRIMARY KEY,
>   lname varchar(15) NOT NULL,
>   fname varchar(15) NOT NULL,
>   job_title varchar(32),
>   org_id int DEFAULT '0' NOT NULL
>           REFERENCES Organizations(org_id)
>           ON UPDATE CASCADE
>           ON DELETE RESTRICT,
>   site_name varchar(64),
>   site_addr varchar(32),
>   site_city varchar(16),
>   state_code char(2),
>   site_postcode varchar(10),
>   site_country char(2) DEFAULT 'US' NOT NULL,
>   direct_phone varchar(15),
>   direct_fax varchar(15),
>   cell_phone varchar(15),
>   site_phone varchar(15),
>   ext varchar(6),
>   email varchar(64),
>   active boolean DEFAULT TRUE NOT NULL,
>   comment text
> );
>
> What I should have noticed when I designed this tool is that addresses and
> phone/e-mail addresses can be duplicated when there's only a single
> location. Now I have some prospective clients with multiple locations but I
> have no names of individuals. So, I want to add a Location table with
> addresses and contact information. Each row in that table will have a
> serial PK
> and will use a FK to reference the Organization table. People will now
> reference the Locations table rather than the Organization table.
>
> There are data in each of these tables and my research in my books and on
> the web have not provided any insights on how to modify the existing schema
> and get date into their new appropriate table homes.
>
> I think the long way is to dump the database and manually move rows (using
> emacs) from their current table to the new one, as appropriate, but
> there're
> probably much better ways to do this and I'm eager to learn.

You could use INSERT INTO location(new_fields,) SELECT the_fields FROM
the_table(s).

>
> Regards,
>
> Rich
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com