User 2 User Support
http://vertica-forums.com/
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)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.
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)