6

I am transitioning from SQL Server to Vertica. Is there any comparable way to create a variable?

Usually I do something like:

Define @myVariable int
Set @myVariable = select MAX(Field1) from myTable
ScottieB
  • 3,958
  • 6
  • 42
  • 60
  • Vertica is based on Postgres, so I would expect Postgres-style declarations to work (http://www.postgresql.org/docs/9.1/static/plpgsql-declarations.html). – Gordon Linoff Jun 11 '13 at 01:34
  • @GordonLinoff Vertica is a column-oriented database, PostgerSQL is not. How can Vertica be "based" on Postgres? – mauro Feb 05 '16 at 00:20
  • @Mauro . . . The license for Postgres code permits developers to modify the code *and re-sell it*. Many more recent databases are based on Postgres -- Greenplum, ParAccel, Netezza, Redshift, for example. Actually, this isn't a big secret. The Postgres Wiki proudly proclaims the many derivative databases: https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases. – Gordon Linoff Feb 05 '16 at 03:01
  • @GordonLinoff exactly! And this is what they say:"Column-oriented DataWarehouse (created by Stonebraker), may only be forking the psql client library." – mauro Feb 05 '16 at 03:09

4 Answers4

5

I do not think Vertica allows variables, except if you are using vsql directly, but then vsql variables are very limited and will not do what you expect:

-- this will work
\set a foo
\echo :a
foo

-- this is not what you expect:
\set a NOW()
\echo :a
NOW()

\set a select max(id) from s.items()
\echo :a
selectmax(id)froms.items()

See for more information the vertica doc at https://my.vertica.com/docs/6.1.x/HTML/index.htm#2732.htm

Guillaume
  • 2,325
  • 2
  • 22
  • 40
  • I should clarify: I'm running my queries in DBeaver, so I can't use this trick. Perhaps the issue is not one of Vertica necessarily? – ScottieB Jun 11 '13 at 16:24
  • 1
    It is Vertica itself which does not allow variables, except under a very limited form via vsql. Your other workaround are programming or subqueries. – Guillaume Jun 13 '13 at 06:25
  • you should put your query in apostrophes and braces if you want to use it in SQL later. E.g.: `\set a '(select max(id) from s.items())'` – Vajk Hermecz Oct 19 '21 at 13:48
2

You do not "create variables" in Vertica the same way you do not "create variables" in SQL Server. What you're trying to convert is a T-SQL script.

You can do the same in Vertica by creating Perl or Python or Java ... scripts running outside the database or writing a user defined function in C++ or R or Java running inside Vertica.

mauro
  • 5,730
  • 2
  • 26
  • 25
0

You can use :variable_name in Vertica for a user input variable. For example:

select date_time from table_1 where date_time between :start and :end

In above start and end are the variables. When you run the query, a dialog box opens prompting you to enter the values for start and end.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
tjt
  • 620
  • 2
  • 7
  • 17
0

If you are using vsql, you can create variables that contain results of queries, though it is a bit convoluted:

Lets assume you start vsql with vsql -v INARG=33;

SELECT :INARG+1;                -- Set up the query
\pset format u
\pset t                         -- Update output format to bare
\g `echo /tmp/dyneval`          -- Eval the query and write into file
\set DYNARG `cat /tmp/dyneval`  -- Set var from shell command output
\echo :DYNARG

So basically we write the query result into a file and read the file's contents into a variable.

You can use /tmp/dyneval-${PPID}_id instead of /tmp/dyneval, so you can ensure, that parallel executions wont alter each other. (PPID being the parent process's process ID, that is the vsql process's PID.)

The solution has some limitations:

  • assumes a linux env (echo, cat)
  • it changes the output formatting settings (\pset)
Vajk Hermecz
  • 5,413
  • 2
  • 34
  • 25