Data Types¶

The SQL type system determines the compile-time and runtime type of an expression. Each type has a certain range of permissible values that can be assigned to a column or value of that type.

The special value NULL, denotes an unassigned or missing value of any of the types (columns that have been assigned as non-nullable using NOT NULL clause or the primary key columns cannot have a NULL value). The supported types are given below.

ARRAY¶

A column of ARRAY datatype can contain a collection of elements. A column of type Array can store array of Java objects (Object[]), typed arrays, java.util.Collection and scala.collection.Seq. You can use com.pivotal.gemfirexd.snappy.ComplexTypeSerializer class to serialize the array data in order to insert it into column tables. Refer How to store and retrieve complex data types in JDBC programs for a Scala example that shows how to serialize and store an array in a table using JDBC APIs and ComplexTypeSerializer class.

SQL Example

# Create a table with column of type of an array of doubles and insert few records
CREATE TABLE IF NOT EXISTS Student(rollno Int, name String, marks Array<Double>) USING column;
INSERT INTO Student SELECT 1,'John', Array(97.8,85.2,63.9,45.2,75.2,96.5);

BIGINT¶

Provides 8 bytes storage for long integer values. An attempt to put a BIGINT value into another exact numeric type with smaller size/precision (e.g. INT) fails if the value overflows the maximum allowable by the smaller type.

BINARY¶

This is a synonym of BLOB.

BLOB¶

A binary large object represents an array of raw bytes of varying length.

{ BLOB | BINARY LARGE OBJECT } [ ( length [{ K | M | G }] ) ] 

The length of the BLOB is expressed in number of bytes by default. The suffixes K, M, and G stand for kilobyte, megabyte and gigabyte, and use the multiples of 1024, 1024*1024, or 1024*1024*1024 respectively.

CREATE TABLE blob_data(id INT primary key, data BLOB(10M)); 
–- search for a blob 
select length(data) from blob_data where id = 100;

BOOLEAN¶

The data type representing Boolean values. This is equivalent to Java's boolean primitive type.

BYTE¶

The data type representing Byte values. It is an 8-bit signed integer (equivalent to Java's byte primitive type).

CHAR¶

Provides for fixed-length strings. If a string value is shorter than the expected length, then spaces are inserted to pad the string to the expected length. If a string value is longer than the expected length, then any trailing blanks are trimmed to make the length same as the expected length, while an exception is raised if characters other than spaces are required to be truncated. For comparison operations, the shorter CHAR string is padded with spaces to the longer value. Similarly when mixing CHARs and VARCHARs in expressions, the shorter value is padded with spaces to the length of the longer string.

To represent a single quotation mark within a string, use two quotation marks:

VALUES 'going to Chandra's place' 

The length of CHAR is an unsigned integer constant.

CHAR[ACTER] [(length)] 

CLOB¶

A character large object represents an array of characters of varying length. It is used to store large character-based data such as documents.

The length is expressed in number characters, unless you specify the suffix K, M, or G, which uses the multiples of 1024, 1024*1024, or 1024*1024*1024 respectively.

{ CLOB | CHARACTER LARGE OBJECT } [ ( length [{ K | M | G }] ) ] 

CREATE TABLE clob_data(id INT primary key, text CLOB(10M)); 
–- search for a clob
select text from clob_data where id = 100;

DATE¶

Provides for storage of a date as year-month-day. Supported formats are:

yyyy-mm-dd 

mm/dd/yyyy 

dd.mm.yyyy 

The year (yyyy) must always be expressed with four digits, while months (mm) and days (dd) may have either one or two digits. DATEs, TIMEs, and TIMESTAMPs must not be mixed with one another in expressions except with an explicit CAST.

VALUES '2010-05-04'

VALUES DATE('2001-10-12')

The latter example uses the DATE() function described in the section Built-in functions and procedures.

DECIMAL¶

Provides an exact decimal value having a specified precision and scale. The precision is the total number of digits both to the left and the right of the decimal point, and the scale is the number of digits in the fraction to the right of the decimal point.

A numeric value (e.g. INT, BIGINT, SMALLINT) can be put into a DECIMAL as long as non-fractional precision is not lost else a range exception is thrown (SQLState: "22003"). When truncating trailing digits from a DECIMAL, the value is rounded down.

{ DECIMAL | DEC } [(precision [, scale ])]

-- this cast loses fractional precision 
values cast (23.8372 AS decimal(4,1)); 
-–- results in: 
23.8 
-- this cast is outside the range 
values cast (97824 AS decimal(4,1)); 
–-- throws exception: 
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(4,1). 

DOUBLE¶

Provides 8-byte storage for numbers using IEEE floating-point notation.

Arithmetic operations do not round their resulting values to zero. If the values are too small, you will receive an exception. Numeric floating point constants are limited to a length of 30 characters.

–- examples of valid values 
values 233.31E3; 
values 8928E+06; 
-- this example will throw a range exception (SQLState: "42820") 
values 123456789012345678901234567890123456789e0; 

FLOAT¶

Alias for a REAL or DOUBLE data type, depending on the specified precision. The default precision is 53 making it equivalent to DOUBLE. A precision of 23 or less makes FLOAT equivalent to REAL while greater than 23 makes it equivalent to DOUBLE.

FLOAT [(precision)]

INT¶

This is a synonym of INTEGER.

INTEGER (INT)¶

Provides 4 bytes storage for integer values. INT can be used as a synonym for INTEGER in CREATE TABLE.

LONG¶

The data type representing Long values. It's a 64-bit signed integer (equivalent to Java's long primitive type).

MAP¶

A column of MAP datatype can contain a collection of key-value pairs.

SQL Examples

# Create a table with column of type MAP and insert few records
CREATE TABLE IF NOT EXISTS StudentGrades (rollno Integer, name String, Course Map<String, String>) USING column;
INSERT INTO StudentGrades SELECT 1,'Jim', Map('English', 'A+');
INSERT INTO StudentGrades SELECT 2,'John', Map('English', 'A', 'Science', 'B');

# Selecting grades for 'English'

snappy> select ROLLNO, NAME, course['English'] from StudentGrades;

ROLLNO  |NAME  |COURSE[English]     
---------------------------
2       |John  |A                                                                                          
1       |Jim   |A+         

A column of type Map can store java.util.Map or scala.collection.Map. You can use com.pivotal.gemfirexd.snappy.ComplexTypeSerializer class to serialize the map data in order to insert it into column tables. Refer How to store and retrieve complex data types in JDBC programs for a Scala example that shows how to serialize and store an array in a table using JDBC APIs and ComplexTypeSerializer class. Map data can also be stored in a similar way.

NUMERIC¶

Synonym for the DECIMAL data type.

NUMERIC [(precision [, scale ])]

REAL¶

Provides a 4-byte storage for numbers using IEEE floating-point notation.

SHORT¶

This is a synonym for SMALLINT.

SMALLINT (TINYINT) (SHORT)¶

Provides 2 bytes storage for short integer values.

STRING¶

The data type representing String values. A String encoded in UTF-8 as an Array[Byte], which can be used for comparison search.

STRUCT¶

A column of struct datatype can contain a structure with different fields.

SQL Examples

# Create a table with column of type STRUCT and insert few records.

CREATE TABLE IF NOT EXISTS StocksInfo (SYMBOL STRING, INFO STRUCT<TRADING_YEAR: STRING, AVG_DAILY_VOLUME: LONG, HIGHEST_PRICE_IN_YEAR: INT, LOWEST_PRICE_IN_YEAR: INT>) USING COLUMN;
INSERT INTO StocksInfo SELECT 'ORD', STRUCT('2018', '400000', '112', '52');
INSERT INTO StocksInfo SELECT 'MSGU', Struct('2018', '500000', '128', '110');

# Select symbols with average daily volume is more than 400000

SELECT SYMBOL FROM StocksInfo WHERE INFO.AVG_DAILY_VOLUME > 400000;
SYMBOL
-------------------------------------------------------------------------
MSGU       

TIMESTAMP¶

Provides for storage of both DATE and TIME as a combined value. In addition, it allows for fractional seconds having up to six digits. Supported formats are:

yyyy-MM-dd hh:mm:ss[.nnnnnn] 

yyyy-MM-dd-hh.mm.ss[.nnnnnn] 

The year (yyyy) must always be expressed with four digits. Months (MM), days (dd), and hours (hh) may have one or two digits while minutes (mm) and seconds (ss) must have two digits. Microseconds, if present, may have between one and six digits. DATEs, TIMEs, and TIMESTAMPs must not be mixed with one another in expressions except with an explicit CAST.

VALUES '2000-02-03 12:23:04' 
VALUES TIMESTAMP(' 2000-02-03 12:23:04.827') 
VALUES TIMESTAMP('2000-02-03 12:23:04')

The latter examples use the TIMESTAMP() function described in the section Built-in functions and procedures.

TINYINT¶

This is a synonym for SMALLINT.

VARCHAR¶

Provides for variable-length strings with a maximum limit for length. If a string value is longer than the maximum length, then any trailing blanks are trimmed to make the length same as the maximum length, while an exception is raised if characters other than spaces are required to be truncated. When mixing CHARs and VARCHARs in expressions, the shorter value is padded with spaces to the length of the longer string.

The type of a string constant is CHAR, not VARCHAR. To represent a single quotation mark within a string, use two quotation marks:

VALUES 'going to Chandra''s place' 

The length of VARCHAR is an unsigned integer constant.

{ VARCHAR | CHAR VARYING | CHARACTER VARYING }(length)