Hi Bruce and Dave.
Am 08-Mar-99 schrieb Bruce Momjian:
>> is there any smart way to duplicate a table definition (NOT the
>> data in the table).I want to create table(s) from a predefined template
>> and drop them when they are empty .
>
> SELECT * INTO newone FROM table WHERE 1=0;
I may be wrong, but I *think* you only get the base types of each field this
way. You won't get defaults, you won't get triggers or rules attached as per
the original...
For the usual purpose of "select...into", namely to have a handy subset of
the data in a table for some temporary purpose, this is OK. But if you want to
literally copy the structure of a table before dropping the original and
proceeding with the copy, not getting the defaults, triggers and rules is a
pretty major disadvantage.
This may identify me as a total beginner (which I am) but I have a table
definition as a series of SQL statements into which I substitute the desired
table name as I pump them out of my Perl scripts. I use "insert...into"
instead of "select...into". Depending on the triggers and rules involved I add
them before or after the creation of the table structure.
Now that I think about it, having a pre-prepared table with another name and
using "alter table...rename" to swap the original out and the replacement in
before dropping the original would be just as good in that you could choose
the table name dynamically; you would also get to define the defaults etc.,
but you still wouldn't get the triggers and rules. The other downside would
be that you could only use the pre-prepared table once.
Regards, K.
---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Karl Auer (auer@kom.id.ethz.ch) Geschaeft/work +41-1-6327531
Kommunikation, ETHZ RZ Privat/home +41-1-4517941
Clausiusstrasse 59 Fax +41-1-6321225
CH-8092 ZUERICH Switzerland