Data Types
In a relational database, each table column has an associated data type, such as Text, Date, or Number. In Ultorg, a column's data type affects the available format options, the input format for edited values, and permitted calculations in formulas.
Ultorg defines a standard set of data types which is used for all data sources. See the list below.
For SQL-based data sources, Ultorg maps each dialect-specific SQL data type to one of Ultorg's standardized types. For example char, varchar, and text on PostgreSQL all map to the Text data type in Ultorg. When Ultorg formulas are compiled to SQL code, Ultorg will include CAST operations to convert between data types as necessary.
For spreadsheet sources (Excel, Google Sheets, and CSV), Ultorg assigns a data type to each table column based on observed values.
The Whole Number type is a subtype of the Number type. In other words, a formula can use a Whole Number wherever a Number is required. A null value can also be used in place of any other value. There are no other subtypes.
Data Type Indications
The area next to the formula bar shows the data type of the primitive field at the current cell.
For data types not specifically recognized by Ultorg, the word Other is indicated, with the name of the data type from the external data source in parentheses:
Data types can also be displayed in the Fields sidebar. To do this, click the column selection button (
) in the corner, and select either Data Type or Backend Type Name for display. See Showing Additional Properties.
All Data Types
The Example Values heading under each data type shows how constant values of the respective type can be entered in formulas.
Text
A sequence of characters, possibly empty. Also known as a "string" or "char" type in other systems.
Comparisons with functions such as contains and equals ("=") are case-sensitive. For case-insensitive matching, use icontains, or the upper function on both inputs to the comparison.
Example Value
"Amalgamated Holdings Corporation"
Number
A number, possibly fractional. May be negative.
When all values are known to be whole numbers, the more specific Whole Number type may be used instead. Some data sources may support special "Infinity" and "NaN" (not-a-number) values; these are treated as null by most functions. Negative and positive zero are indistinguishable.
External data sources may store numbers at various levels of precision. Values in the formula bar reflect the full available precision, for both display and editing purposes.
Example Values
123.45
0.0
-2.0
Whole Number
A whole number. Also known as an "integer" type in other systems. May be negative. A whole number may be used anywhere a Number value is expected, but not the other way around. A number may be converted to a whole number using the floor or ceil functions.
Example Values
14
0
-3
Time
The time of the day. Some values may include seconds or sub-second precision. Values range from midnight, inclusive, to midnight 24 hours later, exclusive. Values of this type are not associated with any particular time zone, and the displayed hour is never affected by the computer's time zone setting.
Example Values
{17:45}
{17:45:23.142}
Time with Offset
The time of the day combined with a stored time zone offset. This data type should rarely be used, but may sometimes be encountered in external data sources (SQL's "time with time zone" type). Since values of this type do not include a date, it is not possible to display them in a time zone other than the stored one (or UTC). Thus, the display of these values is never affected by the computer's time zone setting. Recommended alternatives are Time or Global Timestamp.
Example Values
{13:45-04}
{17:45:23.142Z}
Date
A calendar date. No particular time zone, nor a time of day, is specified in values of this type. The display of these values is never affected by the computer's time zone setting.
Example Value
{1984-12-31}
Local Timestamp
A calendar date plus the time of the day. No particular time zone is specified in values of this type. The display of these values is never affected by the computer's time zone setting. A value of this type is equivalent to a Date value combined with a Time value. If events may originate in multiple time zones, the Global Timestamp type may be used instead to reduce ambiguity.
Example Values
{1984-12-31 17:45}
{1984-12-31 17:45:23.142}
Global Timestamp
An exact point in time stored relative to UTC. Some data sources store explicit time zone information with each individual value. Depending on formatting settings, timestamps of this type may either be displayed in their original form with an explicit zone offset, or in the local computer's default time zone.
Some functions, such as finding the day of the week (weekday), require values of this type to first be converted to a Local Timestamp, e.g. using the local or localUTC functions.
Example Values
{2018-12-31 17:45Z}
{2018-12-31 18:45+01}
{2018-12-31 18:45+01 Europe/Paris}
SQL Interval
A number of months, days, and seconds, corresponding to the INTERVAL type that is found in some SQL dialects. The number of months is stored without regard to the length of each month. This data type should rarely be used outside of manually written SQL queries, but may sometimes be encountered in external data sources. Values can be created using the sqlInterval function, though the functions addDays and addMonths are preferred for timestamp arithmetic.
Example Values
sqlInterval(0, 0, 3606.23)
sqlInterval(0, -3, 0)
Logical
A logical condition which may be either "true" or "false". Also known as a "boolean" type. Such values are usually encountered as the result of comparisons such as equals ("=") and less ("<"). In open perspectives, logical values are shown as checkboxes.
Possible Values
true
false
XML
XML document or XML fragment. Supported as a dedicated data type in some data sources.
JSON
JSON data. Supported as a dedicated data type in some data sources. JSON data can be a JSON primitive (string, number, boolean, or null), a JSON array, or a JSON object.
Use the jsonParse function to get JSON data from a Text string. Use the jsonValue function to extract primitive values from JSON data.
Comparisons on JSON data are done using the string representation from the data source, which may include variations in whitespace and field order.
Example Value
jsonParse("{\"key1\": \"value1\", \"key2\": [2, 3, 4]}")
Binary
Raw binary data. Supported as a dedicated data type in many data sources.
Example Value
binary("48656c6c6f2c20576f726c6421")
UUID
A 128-bit universally unique identifier (UUID). This standardized format is used for randomly generated identifiers in various file formats and databases.
Example Value
{0eec0232-4382-4947-8515-398867265e61}
Transient Row ID
An internal value that is sometimes used as a row identifier in external database tables that do not define an explicit primary key. Such identifiers are not guaranteed to stay constant from one query to the next, and may thus not be used for filtering.
Other
A value of some other type than the standard ones specifically recognized here. This includes, for instance, user-defined types in external data sources.
Null
A placeholder value used to indicate non-applicable or missing data, or where the result of a formula calculation is undefined. For instance, divide ("/") returns null whenever the divisor is zero, and find returns null when no match is found. Most non-aggregate formula functions also return null if any of their input values are null. This behavior allows formula calculations to proceed without error even if their result is undefined for some of the rows in the calculation, while still allowing real results to be distinguished from undefined ones.
A null value is not the same as a numeric zero or an empty text string. The is function may be used to test if a value is null.
Possible Values
null