How to declare a variable in SQL Script?

In other database platforms it is possible to declare a variable that will hold a single value. Is this possible in Vertica, googling and searching the docs I haven't discovered that it is.

Eg, in Transact SQL:
DECLARE @lastIdentityValue smallint;
SELECT @lastIdentityValue = SCOPE_IDENTITY();


  • Options
    Navin_CNavin_C Vertica Customer
    Hi Neil,

    You can set a variable in this way

    \set variable_name value

    example :
    \set lastIdentityValue "value"

    You can check your variable like this:

    select :lastIdentityValue ;

    Also one more way to check:

    \echo :lastIdentityValue

    In Vertica usage of variable is not very flexible. So you need to use some scripting language to use variables with vertica.

    Hope this helps
  • Options
    Thanks Navin.

    I had tried using this, but found an issue:

    dbadmin=> \set id = LAST_INSERT_ID();

    dbadmin=> SELECT :id;ERROR 4856:  Syntax error at or near "=" at character 8

    Is there a way to have the subject of the assignment evaluated? (eg: so that I get the value of the LAST_INSERT_ID() rather than the text?



  • Options
    Navin_CNavin_C Vertica Customer
    Hi Neil

    You tried 
    dbadmin=> \set id = LAST_INSERT_ID();
    Instead Try this

    dbadmin=> \set id LAST_INSERT_ID

    Currently I dont't think there is way to assign a function to a variable,
    As I said Vertica variables are not very flexible.

    You can get some help with this:

    \set t_pwd 'pwd'
    \set file_path :t_pwd/dir_name

    This is how I use one variable into another variable.

    Hope this helps
  • Options
    Hi all,

    Thanks Navin for answering this; you're right (and Neil is on the right track), that's really all we provide by way of variables at this point.

    That said, if I can just jump in briefly:  In some cases, people find our WITH statement to be useful:


    For recordsets, "CREATE LOCAL TEMP TABLE <name> AS SELECT ..." or "CREATE LOCAL TEMP VIEW <name> AS SELECT ..." (depending on whether you want eager or lazy evaluation, respectively) is functionally equivalent to a variable containing a recordset, though it is certainly a more-cumbersome syntax.

  • Options
    Thanks for this Adam, in particular the lazy evaluation version of a Common Table Expression which I wasn't aware of.

    The concrete issue that I'm trying to solve using a variable is the assignment of the LAST_INSERT_ID() value to a variable, such that it can be used in subsequent statements (both as a value in insert statements, and as an argument supplied to where clause predicates in select statements.

    Issues I've encountered include:
    • Meta-function last_insert_id cannot be used in INSERT
    • Meta-function ("last_insert_id") cannot be used with non-Select clauses
    We're considering moving to using sequences rather than autoincrement constraints as a consequence of this.

    Any advice on this?
    Does using a sequence rather than an autoincrement column constraint bring any performance penalties?



  • Options
    Thanks again Navin.

    Not trying to use a non-existent = assignment operator is the trick! Thanks!

  • Options
    Slight update to this...

    Following from Navin's advice above I'm now using a variable with the auto_increment.

    Would still be interested in any details on the tradeoff between sequences, auto_increment, and identity in terms of performance.


  • Options
    hello everybody 
    would you explain, how can i declare a .(dot) special character in table of sql
    for example like exam.rk@gmail.com  then here not allowed a . character. so kindly give responce to me
    Thank you!

  • Options

    Hi, I used this,  a table instead a variable





            Select     Current_Date as Fecha

    Select Fecha From VariableCompensation.Temp_Calendar


    you can use the field as a result of subquery





  • Options

    is there a way to pass variables while running the queries via jdbc ? If so , could some one send some samples how to achieve that? i am basically looking for equivalent of  vsql -v option in jdbc api's.

Leave a Comment

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