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.

Data Type	Supported for Row Tables	Supported for Column Tables
ARRAY	X	✔
BIGINT	✔	✔
BINARY	✔	✔
BLOB	✔	✔
BOOLEAN	✔	✔
BYTE	✔	✔
CLOB	✔	✔
CHAR	✔	✔
DATE	✔	✔
DECIMAL	✔	✔
DOUBLE	✔	✔
FLOAT	✔	✔
INT	✔	✔
INTEGER	✔	✔
LONG	✔	✔
MAP	X	✔
NUMERIC	✔	✔
REAL	✔	✔
SHORT	✔	✔
SMALLINT	✔	✔
STRING	✔	✔
STRUCT	X	✔
TIMESTAMP	✔	✔
TINYINT	✔	✔
VARCHAR	✔	✔

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.

Equivalent Java type	java.lang.Long
Minimum value	java.lang.Long.MIN_VALUE (-9223372036854775808 )
Maximum value	java.lang.Long.MAX_VALUE (9223372036854775807 )

BINARY

This is a synonym of BLOB.

BLOB

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

Equivalent Java type	java.lang.Blob
Maximum length (also default length)	2 GB - 1 (or 2,147,483,647)

{ 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).

Minimum value	java.lang.Byte.MIN_VALUE
Maximum value	java.lang.Byte.MAX_VALUE

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.

Equivalent Java type	java.lang.String
Maximum length	java.lang.Integer.MAX_VALUE (2147483647 )
Default length	1

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.

Equivalent Java type	java.sql.Clob
Maximum length (also default length)	2 GB - 1 (or 2,147,483,647)

{ 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.

Equivalent Java type	java.sql.Date

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.

Equivalent Java type	java.math.BigDecimal
Precision min/max	1 to 31
Scale min/max	less than or equal to precision
Default precision	5
Default scale	0

{ 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.

Equivalent Java type	java.lang.Double Note: The maximum/minimum limits are different from those of java.lang.Double as noted below.
Minimum value	-1.79769E+30
Maximum value	1.79769E+308
Smallest positive value	2.225E-307
Largest negative value	-2.225E-307
Default precision	5
Default scale	0

–- 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.

Equivalent Java type	java.lang.Double or java.lang.Float depending on precision
Minumum/Maximum limits	Same as those for FLOAT if the precision is less than 23. Otherwise, same minimum/maximum limits as those for DOUBLE.
Default precision	53

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.

Equivalent Java type	java.lang.Integer
Minimum value	java.lang.Integer.MIN_VALUE (-2147483648)
Maximum value	java.lang.Integer.MAX_VALUE (21474836487)

LONG

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

Minimum value	java.lang.Long.MIN_VALUE
Maximum value	java.lang.Long.MAX_VALUE

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.

Equivalent Java type	java.lang.Float
Minimum value	-3.402E+38f
Maximum value	+3.402E+38f
Smallest positive value	+1.175E-37f
Largest negative value	-1.175E-37f

SHORT

This is a synonym for SMALLINT.

SMALLINT (TINYINT) (SHORT)

Provides 2 bytes storage for short integer values.

Equivalent Java type	java.lang.Short
Minimum value	java.lang.Short.MIN_VALUE (-32768 )
Maximum value	java.lang.Short.MAX_VALUE (32767)

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       

A column of type STRUCT can store array of Java objects (Object[]), typed arrays, java.util.Collection, scala.collection.Seq or scala.Product. You can use com.pivotal.gemfirexd.snappy.ComplexTypeSerializer class to serialize the 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.

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.

Equivalent Java type	java.sql.Timestamp

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.

Equivalent Java type	java.lang.String
Maximum length	32672

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