CREATE TABLE LIKE INCLUDING PROJECTIONS (and data?)

[beth]

Hi,

Is there a way to use the new CREATE TABLE LIKE INCLUDING PROJECTIONS syntax to also include the data from the source table?

In this example, I'd like the beth2.table to to have the data from the beth1.table table...

Code: Select all

dbadmin=> create schema beth1;
CREATE SCHEMA
dbadmin=> create schema beth2;
CREATE SCHEMA
dbadmin=> create table beth1.table (c int);
CREATE TABLE
dbadmin=> insert into beth1.table values (1);
 OUTPUT 
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> create table beth2.table like beth1.table including projections;
CREATE TABLE
dbadmin=> select * from beth2.table;
 c 
---
(0 rows)

Is there a "create table beth2.table like beth1.table including projections including data;" option? Probably not...

Do I have to issue an insert statement? That's lame if I do!

[NorbertKrupa]

If you look at the CREATE syntax in the https://my.vertica.com/docs/6.1.x/HTML/ ... m#1293.htm,

Code: Select all

CREATE TABLE [ IF NOT EXISTS ] [[db-name.]schema.]table-name 
{
... ( column-definition (table)  [ , ... ] ) 
... | [ table-constraint ( column_name, ... )]
... | [ column-name-list  (create table)  ] AS [COPY] [ [ AT EPOCH LATEST ] 
... | [ AT TIME 'timestamp' ] ] [ /*+ direct */ ] query 
... | [ LIKE [[db-name.]schema.]existing-table [ INCLUDING PROJECTIONS | EXCLUDING PROJECTIONS ] ]
}

... looks to me like there are 5 ways to define the structure. It seems logical to only allow one or the other, that is [AS query] or [LIKE table] since the structure gets defined when those options are used. I tried a few different combinations, but it seems only one is allowed. I think you'll have to take the lame route