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.
How to copy a table with no data?
Moderator: NorbertKrupa
How to copy a table with no data?
Thank you!
Joshua
Joshua
Re: How to copy a table with no data?
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.
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.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to copy a table with no data?
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:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: How to copy a table with no data?
Thanks guys! That exactly what I was looking for!
Thank you!
Joshua
Joshua
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: How to copy a table with no data?
Hi Navin,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.
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
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to copy a table with no data?
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:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: How to copy a table with no data?
Thank you nicely..
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1