Issue with 'create table as select from'

Hi,

 

I am trying to run the follwoing but get [Vertica][VJDBC](4856) ERROR: Syntax error at or near "AS" :-

 

create table reporting_staging.temp_compSMBmembers2
("col1" integer, 
col2 varchar(20)
)
AS
select col1,col2
from table
where year(date) = '2011' and month(date) = '09' ;

 

It looks like according to the docs it should work?

 

Any ideas?

 

Thanks,

Comments

  • SruthiASruthiA Vertica Employee Administrator

    Hi Newman,

     

         The syntax of the create table statement should be corrected slightly.

     

    1) We do not specify data types of the column names while creating a table from select query. it inherits data type from the columns of select query.  Please try executing the following query. 

     

    create table reporting_staging.temp_compSMBmembers2
    (col1, col2)
    AS
    select col1,col2
    from table
    where year(date) = '2011' and month(date) = '09' ;

     

    for more information, please refer to the documentation http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATETABLE.htm?Highlight=create table

     

    -Regards,

     Sruthi

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file