How to copy a table with no data?

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

How to copy a table with no data?

Post by Josh » Thu Apr 05, 2012 9:16 pm

Is there a way to quickly create or copy a table based on another table? I want to discard the data and just need the structure. We're creating load tables from our standard tables. If I do a create table as select I get all the data and then have to truncate the table.

thanks.
Thank you!
Joshua

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: How to copy a table with no data?

Post by nnani » Fri Apr 13, 2012 7:11 am

Hi Josh,

As per my understanding you requirement is to just copy the table structure

Hope this query can help you:

create table foo_new as select * from foo limit 0;

Let me know if this helps you.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1823
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to copy a table with no data?

Post by JimKnicely » Fri Apr 13, 2012 11:46 am

Navin: I like the way you used the LIMIT command! I didn't think of that! I'm an old Oracle guy and we didn't have the LIMIT command so we had to use the WHERE 1=2 trick, but I like your way better!

Examples:

Code: Select all

dbadmin=> create table test1 (col1 int, col2 varchar(100));
CREATE TABLE
dbadmin=> insert into test1 values (1, 'Hi there!');
 OUTPUT
--------
      1
(1 row)

dbadmin=> create table test2 as select * from test1 limit 0;
CREATE TABLE
dbadmin=> select * from test2;
 col1 | col2
------+------
(0 rows)

dbadmin=> \d test2;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | test2 | col1   | int          |    8 |         | f        | f           |
 public | test2 | col2   | varchar(100) |  100 |         | f        | f           |
(2 rows)

dbadmin=> create table test3 as select * from test1 where 1=2;
CREATE TABLE
dbadmin=> select * from test3;
 col1 | col2
------+------
(0 rows)

dbadmin=> \d test3;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | test3 | col1   | int          |    8 |         | f        | f           |
 public | test3 | col2   | varchar(100) |  100 |         | f        | f           |
(2 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Re: How to copy a table with no data?

Post by Josh » Fri Apr 13, 2012 3:08 pm

Thanks guys! That exactly what I was looking for!
Thank you!
Joshua

rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Re: How to copy a table with no data?

Post by rajasekhart » Wed Apr 25, 2012 11:09 am

Hi Josh,

As per my understanding you requirement is to just copy the table structure

Hope this query can help you:

create table foo_new as select * from foo limit 0;

Let me know if this helps you.
Hi Navin,

As per the above discussion
If the table foo is containing an Auto Increment Column, does it(Auto Increment Property) also exists in the column of new table foo_new?
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1823
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to copy a table with no data?

Post by JimKnicely » Wed Apr 25, 2012 3:57 pm

Hey,

Great question! The answer is no... When you do a CTAS in Vertica, the IDENTITY property does not propagate to the new table:

Example:

Code: Select all

dbadmin=> create table foo (col1 auto_increment, col2 varchar(100), primary key(col1));
CREATE TABLE
dbadmin=> \d foo;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | foo   | col1   | int          |    8 |         | t        | t           |
 public | foo   | col2   | varchar(100) |  100 |         | f        | f           |
(2 rows)
                                      ^
dbadmin=> create table foo2 as select * from foo where 1 = 2;
CREATE TABLE
dbadmin=> select * from foo2;
 col1 | col2
------+------
(0 rows)

dbadmin=> select table_name, column_name, is_identity from columns where table_name IN ('foo', 'foo2') and column_name = 'col1';
 table_name | column_name | is_identity
------------+-------------+-------------
 foo        | col1        | t
 foo2       | col1        | f
(2 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Re: How to copy a table with no data?

Post by rajasekhart » Thu Apr 26, 2012 11:11 am

Thank you nicely.. :)
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

Post Reply

Return to “New to Vertica”