Thread: creating a table from another table

creating a table from another table

From
"David O'Farrell"
Date:
-- 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 .




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  David O'Farrell                              AerSoft Limited
  mailto:dave.ofarrell@aersoft.ie              2 Northumberland Avenue,
                                               Dun Laoghaire,Co. Dublin
    Direct Phone 353-1-2145950
               Phone: 01-2301166   Fax: 01-2301167
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Re: [GENERAL] creating a table from another table

From
Bruce Momjian
Date:
>
> -- 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;

--
  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

Re: [GENERAL] creating a table from another table

From
Karl Auer
Date:
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

Re: [GENERAL] creating a table from another table

From
Bruce Momjian
Date:
[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