Re: Modifying database schema without losing data - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Modifying database schema without losing data |
Date | |
Msg-id | 945982c5-197f-d3b3-5c15-010cff998bdc@aklaver.com Whole thread Raw |
In response to | Modifying database schema without losing data (Rich Shepard <rshepard@appl-ecosys.com>) |
Responses |
Re: Modifying database schema without losing data
|
List | pgsql-general |
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
pgsql-general by date: