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.klaver@aklaver.com