Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0HGub035587 for ; Wed, 17 Jan 2001 11:56:37 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [208.59.253.44] (HELO agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.3) with ESMTP id 1178312 for pgsql-sql@postgresql.org; Wed, 17 Jan 2001 08:56:32 -0800 Message-ID: <3A65CDEC.46BA1B6E@agliodbs.com> Date: Wed, 17 Jan 2001 08:53:00 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions X-Mailer: Mozilla 4.73 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Yet one more question References: <3A64543B.154F29B0@bigpond.net.au> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/193 X-Sequence-Number: 857 Justin, > How do you do the "drop and create" of tables? 1. Save table definition as text. 2. Create a duplicate of the table definition as "temp_table" 3. INSERT all of the table records into the temp_table 4. DROP the existing table 5. Re-CREATE the table with the altered definition. 6. INSERT the rows from the temp_table back into the table. Of course, this process fails to preserve SERIAL keys, FORIEGN KEYS, etc. and is somewhat labor intensive. ANybody create a script to do this dynamically? > I use pg_dump -d > something.sql > > Then I use vi/sed/something-else to modify the schema in the dumped > file, > then reload it into postgreSQL with psql -e < something.sql > > /dev/null Thanks. SOunds like a good alternate strategy, although it still blows away our test data. I'd also need to see if our functions survive the dump ... -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco