Thread: create table as vs. create table like
I just noticed something I found "unexpected". CREATE TABLE LIKE let you specify DEFAULT and Co. CREATE TABLE AS doesn't. Is there a one step way to clone a table? -- Ivan Sergio Borgonovo http://www.webthatworks.it
In response to Ivan Sergio Borgonovo : > I just noticed something I found "unexpected". > > CREATE TABLE LIKE let you specify DEFAULT and Co. > CREATE TABLE AS doesn't. > > Is there a one step way to clone a table? with or without data? create table new_table (like old_table) create table new_table as select * from old_table Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Andreas K. wrote:
>
> In response to Ivan Sergio Borgonovo :
> > I just noticed something I found "unexpected".
> >
> > CREATE TABLE LIKE let you specify DEFAULT and Co.
> > CREATE TABLE AS doesn't.
> >
> > Is there a one step way to clone a table?
>
> with or without data?
>
> create table new_table (like old_table)
> create table new_table as select * from old_table
In addition:
CREATE TABLE new_table AS SELECT * FROM old_table LIMIT 0; -- also gets you a clone w/ no data
In 8.3 the (LIKE ...) construct allows for creation of indexes; in earlier versions I think they have to be done manually if they are desired. I think though that populating the table w/ indexes from a LIKE command would need an extra step to load data [which seems odd, seems it much faster usually to load data and then create indexes, etc.].
HTH,
Greg Williamson
Senior DBA
DigitalGlobe
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
On Fri, 12 Dec 2008 13:25:07 +0100 "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > In response to Ivan Sergio Borgonovo : > > I just noticed something I found "unexpected". > > > > CREATE TABLE LIKE let you specify DEFAULT and Co. > > CREATE TABLE AS doesn't. > > > > Is there a one step way to clone a table? > > with or without data? > > create table new_table (like old_table) > create table new_table as select * from old_table They achieve partially what I'm looking for. Not that I really need to save 100 bytes but as said it looked "unexpected" since most of what's needed is almost there but is split across the 2 commands in a "strange way". create table like clones the "structure" including indexes, default and constraint but not the content. create table as clones the schema and the content but not the indexes, default and constraints. create table newtable (like oldtable including defaults, constraints, indexes); -- I should test the syntax for multiple "including" insert into newtable select * from oldtable; Is the best thing that come close to cloning a table. On the top of my head CHECKS will still miss. Without cloning indexes, defaults, constraints create table as does look really redundant (select into newtable) and create table like is "almost there". Gregory's remark about index creation is interesting... but well it just would depend on the implementation. -- Ivan Sergio Borgonovo http://www.webthatworks.it