DBeaver Documentation

DOWNLOAD pdf

Virtual column expressions

Expression language

You can use standard JavaScript-like expression language. DBeaver uses the Jexl engine to process expressions.
Language references and examples can be found here: http://commons.apache.org/proper/commons-jexl/reference/syntax.html

Column values

All columns' values in the current result set can be referred to by name. Expression column1 + column2 will produce the sum of two numeric columns or concatenation of two string columns column and column2.

Standard functions

Standard functions are declared in namespaces.
You can refer to the functions in the namespaces as variables - nsName.functionName(parameters).

math

You can access all math functions as math.function(parameters).
You can find all supported math functions here: https://docs.oracle.com/cd/E12839_01/apirefs.1111/e12048/functmath.htm

geo

Function Parameters Description
wktPoint(longitude, latitude)Produces WKT (geometry) point out of two coordinates. Default SRID is 4326.
wktPoint(longitude, latitude, srid)Produces WKT (geometry) point out of two coordinates and SRID

content

If you have JSON or XML columns in your table, you can add a virtual column with an expression for these columns.

Use content.json()[parameter1][parameter2] pattern to create expression for JSON column.

Expression example: content.json(column1)['glossary']['GlossDiv']['title']

You can read more about JSON parameters in the link on the top.

Use content.xml(columnName, "expression") or content.xml(columnName, "returnType", "expression") patterns to create expression for XML column. The quotation is important for parsing processes. XML expression can return types: string, number, boolean, node, nodeset - all these types can be used (!in quotes) for return data type clarification. content.xml(columnName, "expression") returns string by default

Expression example: content.xml(column1, "nodeset", "/Employees/Employee[gender='Female']/name/text()")

You can read more about XPath here: https://en.wikipedia.org/wiki/XPath

Defining Virtual Keys

To be able to save column value changes, a table must have some unique key (primary key or unique index). Some databases (Oracle, DB2, PostgreSQL) support a special virtual unique column that DBeaver can use to save changes. In other cases, you can define a virtual key – a set of columns that forms a unique combination of values. When you try to save changes in a table without a unique key, DBeaver displays the following error message:

To use all columns as the virtual key, click Use All Columns. To create a custom key, click Custom Unique Key. Alternatively, to create a unique custom key, you can click the Configure () button on the bottom toolbar and then click Define virtual unique key** on the Configure menu. The Define virtual unique identifier window opens:

To define the key, select some of the columns or click Select All and then click OK. To remove a unique key from a table, click the Configure button in the bottom toolbar and then click Clear virtual unique key.