Thread: creating a table from another table
-- 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > -- 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
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
[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