The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Create table with a variable name

Hi,  I need to create tables on daily basis with name as date in form at (yyMMdd), I tried this :

 

dbadmin=> \set table_name 'select to_char(current_date, \'yyMMdd \')'
dbadmin=> :table_name;
to_char
---------
150515
(1 row)

 

 

and then tried to create table with table name from the set parameter :table_name, but got this

 

dbadmin=> create table :table_name(col1 varchar(1));
ERROR 4856: Syntax error at or near "select" at character 14
LINE 1: create table select to_char(current_date, 'yyMMdd ')(col1 va...

 

 

 

Is there a way where i could store a value in a variable and then use that variable as table name or to assign priority that the inner select statement has exeute first to give me the name i require.Please suggest!!!

Comments

  • Try this :

     

     

    - you will need to store the result and not the select string 

     

    ([email protected]:5433) [dbadmin] *> \set table_name `vsql -U dbadmin -w d -t -c "select concat('tbl_',replace(to_char(current_date, 'yyMMdd'),' ',''))"`
    ([email protected]:5433) [dbadmin] *> \echo :table_name
    tbl_150515

    ([email protected]:5433) [dbadmin] > create table :table_name(col1 varchar(1));
    CREATE TABLE

    ([email protected]:5433) [dbadmin] > \d tbl_150515;
    List of Fields by Tables
    Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
    --------+------------+--------+------------+------+---------+----------+-------------+-------------
    public | tbl_150515 | col1 | varchar(1) | 1 | | f | f |
    (1 row)


Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.