Re: [GENERAL] creating a table from another table

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 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.

Your answer is much better than mine.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026