SQL Data Types¶

Data Types¶

The following data types are supported:

Char Data Type¶

Char strings are fixed-length strings. Char strings can contain any printing or non-printing character, except the null character ('\0').

In uncompressed tables, char strings are padded with blanks to the declared length. For example, if ABC is entered into a char(5) column, five bytes are represented, as follows:

'ABC  '

Leading and embedded blanks are significant when comparing char strings. For example, the following char strings are considered different:

'A B C''ABC'

When selecting char strings using the underscore (_) wildcard character of the LIKE predicate, include any trailing blanks to be matched. For example, to select the following char string:

'ABC '

the wildcard specification must also contain trailing blanks:

'_____'

Length is not significant when comparing char strings; the shorter string is (logically) padded to the length of the longer. For example, the following char strings are considered equal:

'ABC''ABC '

Varchar Data Type¶

Varchar strings are variable-length strings. Varchar strings can contain any character, including non-printing characters, except the ASCII null character ('\0').

If the column is nullable, varchar columns require an additional byte of storage.

Blanks are significant in the varchar data type. For example, the following two varchar strings are not considered equal:

'the store is closed'

and

'thestoreisclosed'

If the strings being compared are unequal in length, the shorter string is padded with trailing blanks until it equals the length of the longer string.

For example, consider the following two strings:

'abcd\001'

where:

'\001' represents one ASCII character (ControlA)

and

'abcd'

If they are compared as varchar data types, then

'abcd' > 'abcd\001'

because the blank character added to 'abcd' to make the strings the same length has a higher value than ControlA ('\040' is greater than '\001').

Nchar Data Type¶

Actian stores all character data in UTF8 so the nchar data type is equivalent to the char data type.

Nvarchar Data Type¶

Actian stores all character data in UTF8 so the nvarchar data type is equivalent to the varchar data type.

Integer Data Types¶

Exact numeric data types include the following integer data types:

• INTEGER1 or TINYINT (one-byte)

• INTEGER 2 or SMALLINT (two-byte)

• INTEGER4 or INTEGER (four-byte)

• INTEGER8 or BIGINT (eight-byte)

The following table lists the ranges of values for each integer data type:

Decimal Data Type¶

The decimal data type is an exact numeric data type defined by its precision (total number of digits) and scale (number of digits to the right of the decimal point). For example:

Specify the decimal data type using the following syntax:

decimal(p,s)

where:

p

Defines the precision. Minimum is 1; maximum is 38.

s

Defines the scale. The scale of a decimal value cannot exceed its precision. Scale can be 0 (no digits to the right of the decimal point).

The following defines a type with precision of 23 decimal digits, 5 of which are after the decimal point:

DECIMAL(23,5)

Floating Point Data Types¶

A floating point value is represented either as whole plus fractional digits (like decimal values) or as a mantissa plus an exponent.

The following is an example of the mantissa and exponent parts of floating point values:

There are two floating point data types:

• FLOAT4 (4-byte)
• FLOAT (8-byte)

A synonym for FLOAT4 is REAL. Synonyms for FLOAT are FLOAT8 and DOUBLE PRECISION.

Floating point numbers are stored in four or eight bytes. Internally, eight-byte numbers are rounded to fifteen decimal digits. The precision of four-byte numbers is processor dependent.

You can specify the minimum required binary precision (number of significant bits) for a floating point value using the following optional syntax:

FLOAT(n)

where n is a value from 0 to 53. Storage is allocated based on the precision that is specified, as follows:

Floating point precision is not limited to the declared size.

Float Point Limitations¶

You must consider the effects of data type conversions when numeric values are combined or compared. This is especially true when dealing with floating point values.

Exact matches on floating point numbers are discouraged, because FLOAT and FLOAT4 data types are approximate numeric values. In contrast, INTEGER and DECIMAL data types are exact numeric values.

Here is an example of why it is hard to find an exact match on a floating point number:

test(f8 FLOAT8);

INSERT INTO test VALUES(330/11.0);

SELECT * FROM test WHERE f8 = 30;

returns no rows.

On inspection,

SELECT HEX(f8) FROM test;

returns:

+----------------+

|col1            |

+----------------+

|403E000000000001|

+----------------+

Using an IEEE analyzer we find this number to be decimal 30.000000000000003552713678800500929355621337890625, which, though close to 30, shows why no exact match was found.

To ensure the query returns a row, you can change it to the following:

SELECT * FROM test WHERE ABS(f8 - 30) < 0.0000000001

Money Data Type¶

The MONEY data type is an abstract data type. Money values are stored significant to two decimal places. These values are rounded to their amounts in dollars and cents or other currency units on input and output, and arithmetic operations on the money data type retain two-decimal-place precision.

Money columns can accommodate the following range of values:

$-999,999,999,999.99 to $999,999,999,999.99

A money value can be specified as either:

• A character string literal – The format for character string input of a money value is$sdddddddddddd.dd. The dollar sign is optional and the algebraic sign (s) defaults to + if not specified. There is no need to specify a cents value of zero (.00).
• A number – Any valid integer or floating point number is acceptable. The number is converted to the money data type automatically.

On output, money values display as strings of 20 characters with a default precision of two decimal places. The display format is:

$[-]dddddddddddd.dd

where:

$ is the default currency symbol dis a digit from 0 to 9

The following settings affect the display of money data.:

SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;

Ansidate Data Type¶

The ANSIDATE data type consists of a year, month, day. Input and output is in the format yyyy-mm-dd.

The declaration format is as follows:

ANSIDATE

A synonym for ANSIDATE is DATE.

Time Data Types¶

The TIME data type consists of a time in hour, minutes, seconds, optional fractions of a second, and optional time zone.

The format is as follows:

TIME [time_precision] [time_zone_spec]

time_precision

(Optional) Indicates the number of digits of precision in the fractions of seconds, as an integer value from 0 to 9. When no time precision is supplied, the value of time_precision is set to 0 by default.

time_zone_spec

(Optional) Specifies a time zone as one of the following:

• WITH TIME ZONE

• WITHOUT TIME ZONE

• WITH LOCAL TIME ZONE

Examples:

Timestamp Data Types¶

The TIMESTAMP data type consists of a date and time, with optional time zone.

The format is as follows:

TIMESTAMP [(timestamp_precision)] [time_zone_spec]

timestamp_precision

(Optional) Indicates the number of digits of precision in the fractions of seconds, as an integer value from 0 to 9. The default is 6.

time_zone_spec

(Optional) Specifies a time zone as one of the following:

• WITH TIME ZONE

• WITHOUT TIME ZONE

• WITH LOCAL TIME ZONE

Examples:

Note:Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the database. For example:

TIMESTAMP '2012-12-15 9:30:55 -8:00'

is the same as

TIMESTAMP '2012-12-15 12:30:55 -5:00'

Interval Data Types¶

The INTERVAL data types include year to month and day to second intervals.

The format is as follows:

INTERVAL interval_qualifier

interval_qualifier

Defines an interval column as one of the following:

• YEAR TO MONTH

• DAY TO SECOND [(second_precision)]

where second_precision is the number of digits in the fractions of seconds field.

Examples:

Boolean Data Type¶

BOOLEAN columns accept as input the literal values TRUE and FALSE and the strings 'TRUE' and 'FALSE', with no case sensitivity.

Internally, the BOOLEAN type is stored as a single-byte integer that takes only the values 0 and 1.

IP Network Address Data Types¶

IPV4 and IPV6 are abstract data types that store IPv4 and IPv6 host addresses, respectively, in binary format.

IPV4 is a 4-byte host address in dotted-decimal notation (four decimal numbers, each ranging from 0 to 255, separated by dots). For example: 172.16.254.1.

IPV6 is a 16-byte host address in eight groups of four hexadecimal digits separated by colons. For example: 2001:0db8:85a3:0042:1000:8a2e:0370:7334.

We recommend using the IPV4 and IPV6 data types instead of plain text to store network addresses because they provide input error checking and specialized operators and functions.

An IPv4 address can be stored in an IPv6-type value, but the address will be stored as an IPv4-mapped IPv6 address. When coerced to type IPV6, a “pure” IPv4 address cannot be distinguished from a mapped IPv4 address—for example, "192.168.0.1" cannot be distinguished from "::ffff:c0a8:1" or from "::ffff:192.168.0.1".

Comparisons between two IPV4 or two IPV6 values operate like BYTE comparisons (that is, unsigned byte-wise). IPV6 is the “higher” type in mixed expressions.

IPV4 and IPV6 addresses are stored as 4 and 16 bytes, respectively.

Example input and output:

IPV4 or IPV6 Input	Output
172.16.254.1	172.16.254.1
2001:db8:00:567:8:1	2001:db8:00:567:8:1
2001:db8:0:0:0:0:0:0	2001:db8::
::ffff:c0a8:1	192.168.0.1

Universal Unique Identifier (UUID)¶

A Universal Unique Identifier (UUID) is a 128 bit, unique identifier generated by the local system upon request or loaded from external sources.

The database can generate UUID values with the uuid() function. Existing UUID values can be loaded from external sources; the data type is coerced, if necessary. The system cannot guarantee uniqueness of these external values, but when used correctly, the algorithms used to create the values will guarantee it.

The identifier is unique across both space and time with respect to the space of all UUIDs. UUID values generated are Version 1 (time-based) UUIDs.

A UUID can be used to tag records to ensure that the database records are uniquely identified regardless of which database they are stored in, for example, in a system where there are two separate physical databases containing accounting data from two different physical locations.

No centralized authority is responsible for assigning UUIDs. They can be generated on demand (10 million per second per machine if needed).

A UUID can be used for multiple purposes:

• Tagging objects that have a brief life

• Reliably identifying persistent objects across a network

• Assigning as unique values to transactions as transaction IDs in a distributed system

UUIDs are fixed sized (128 bits), which is small relative to other alternatives. This fixed small size lends itself well to sorting, ordering, and hashing of all sorts, sorting in databases, simple allocation, and ease of programming.

Storage Formats of Data Types¶

The following table describes how each data type is stored:

Nullable columns require one additional byte to store a null indicator.