1. Data Types#

In order to use SQL to store, change, and query the data in a database, it is first necessary to possess a thorough understanding of the available data types. This chapter presents a detailed explanation of the data types supported in Altibase.

Types of Data Types#

The following data types are supported in Altibase:

Character Data Types#

Type	Length	Size
M : Defined column length L : The length of the input string
CHAR(M)	1 ~ 32000	M + 2
VARCHAR(M)	1 ~ 32000	length + 2 where length = L if the input value is stored in a variable area length = M if the input value is stored in a fixed area
NCHAR(M)	1 ~ 16000(UTF16) 1 ~ 10666(UTF8)	M2 + 2(UTF16) M3 + 2(UTF8)
NVARCHAR(M)	1 ~ 16000(UTF16) 1 ~ 10666(UTF8)	length2 + 2(UTF16) length3 + 2(UTF8) where: length = L if the input value is stored in a variable area length = M if the input value is stored in a fixed area

NCHAR and NVARCHAR are Unicode character types. The available maximum length of a UTF16-encoded string is different from that of a UTF8-encoded string.

Numerical Data Types#

Non-native	Type	Precision	Scale	Size (bytes)	Remarks
	NUMERIC	38	0	3+((precision)+2)/2	Fixed-Point Numbers The NUMERIC data type is the same as the DECIMAL datatype.
	NUMERIC(p)	1 ~ 38	0
	NUMERIC(p,s)	1 ~ 38	-84 ~ 128
	DECIMAL	38	0
	DECIMAL(p)	1 ~ 38	0
	DECIMAL(p,s)	1 ~ 38	-84 ~ 128
	NUMBER(p)	1 ~ 38	0
	NUMBER(p,s)	1 ~ 38	-84 ~ 128
	NUMBER	38	X	3+((precision)+2)/2	* Floating-Point Numbers
	FLOAT	38	X
	FLOAT(p)	1 ~ 38	X
Native	Type	Compatible C Type		Size(bytes)	Remarks
	DOUBLE	double		8	*Floating-Point Numbers
	REAL	float		4	*Floating-Point Numbers
	BIGINT	long or long long		8	*Integer Type
	INTEGER	int		4
	SMALLINT	short		2

Example 1

Fixed-Point Numbers Size Calculation:

( 3 + ( ( p ) + 2 ) / 2 )

- NUMERIC  
  NUMERIC(38, 0): Size = 3 + 40/2 = 23 bytes

- NUMERIC(p) / NUMERIC(p, 0)  
  NUMERIC(10): Size = 3 + 12/2 = 9 bytes

- NUMERIC(p, s)  
  NUMERIC(10, 9): Size = 3 + 12/2 = 9 bytes

- DECIMAL: the same as NUMERIC
— DECIMAL(p): the same as NUMERIC(p)
— DECIMAL(p,s): the same as NUMERIC(p,s)
— NUMBER(p): the same as NUMERIC(p)
— NUMBER(p,s): the same as NUMERIC(p,s)

Example 2

Floating-Point Numbers Size Calculation: ( 3 + ( ( p ) + 2 ) / 2 )

- FLOAT  
  FLOAT(38): Size = 3 + 40/2 = 23 bytes

- FLOAT(p)  
  FLOAT(20): Size = 3 + 22/2 = 14 bytes

- NUMBER: the same as FLOAT

Date Data Type#

Type	Size (byte)
DATE	8

Binary Data Types#

Type	Length	Size
M : Defined column length L : The length of the input value
BLOB/CLOB		1 ~ 4294967295
BYTE	1 ~ 32000	M + 2
VARBYTE	1 ~ 32000	length + 2 where length = L if the input value is stored in a variable area length = M if the input value is stored in a fixed area
NIBBLE	1 ~ 254	M/2 + 1
BIT	1 ~ 64000	M/8 + 4
VARBIT	1 ~ 64000	length/8 + 4 where length = L if the input value is stored in a variable area length = M if the input value is stored in a fixed area

Geometry Data Type#

Type	Length	Size (byte)
GEOMETRY	8 ~ 104857600	length + 40

The actual record size is the size of each data type as indicated above, plus the size of header information. The size of the header information varies depending on the OS.

JSON Data Type#

Type	Size(byte)
JSON	2 GB (2,147,483,648)

NULL#

When a row is inserted into a table, the value of a column is set to NULL if the value for that column is not known or has not been determined yet. In other words, NULL indicates that no value exists. Therefore, NULL is not the same as 0 (zero) or blank space, and is handled differently when performing comparison operations or saving data.

If any operation other than the NVL() function or the IS NULL or IS NOT NULL conditions is performed on a NULL value, the final result of the formula containing the operation will be NULL. In other words, comparisons and operations are meaningless when performed on NULL values.

NULL can appear in columns of any data type, as long as they are not restricted by NOT NULL or PRIMARY KEY constraints.

Data Type Conversion#

When performing operations on different data types, type conversion is applied to ensure accurate computation. The conversion can be either implicit or explicit.

Implicit Data Type Conversion#

Implicit conversion refers to the internal transformation of data types when performing operations on values of different types, while maintaining the original data type's properties. When comparing two values of the same data type, the comparison operation is performed directly without any conversion. However, if the two values have different data types, one value is converted to the data type of the other before the comparison is carried out.

The following table shows the matrix of data types that can be implicitly converted (O: indicates that the data type’s properties are preserved after conversion). For information on how to convert a column's data type using the MODIFY clause, refer to the modify_column_clause section in the SQL Reference.

After Conversion ► Before Conversion▼	char	var char	nchar	nvarchar	clob	big int	deci mal	dou ble	float	int eger	num ber	num eric	real	small int	date	blob	byte	varbyte	nibble	bit	varbit	geometry
char	o	o	o	o		o	o	o	o	o	o	o	o	o	o
varchar	o	o	o	o	o	o	o	o	o	o	o	o	o	o	o
nchar	o	o	o	o		o	o	o	o	o	o	o	o	o	o
nvarchar	o	o	o	o	o	o	o	o	o	o	o	o	o	o	o
clob					o
bigint	o	o	o	o		o	o	o	o	o	o	o	o	o
decimal	o	o	o	o		o	o	o	o	o	o	o	o	o
double	o	o	o	o		o	o	o	o	o	o	o	o	o
float	o	o	o	o		o	o	o	o	o	o	o	o	o
integer	o	o	o	o		o	o	o	o	o	o	o	o	o
number	o	o	o	o		o	o	o	o	o	o	o	o	o
numeric	o	o	o	o		o	o	o	o	o	o	o	o	o
real	o	o	o	o		o	o	o	o	o	o	o	o	o
smallint	o	o	o	o		o	o	o	o	o	o	o	o	o
date	o	o	o	o											o
blob																o
byte																o	o	o
varbyte																o	o	o
nibble																			o
bit																				o	o
varbit		o																		o	o
geometry																						o

Implicit Data Type Conversion Rules#

If '1000' of bit type is entered into table t10 table, the conversion succeeds to integer '1000', but it is not an implicit data type conversion because the attribute of data type is changed.

iSQL> create table t10 (i1 integer);
Create success.
iSQL> insert into t10 values (bit'1000');
1 row inserted.
iSQL> select * from t10;
I1
--------------
1000
1 row selected.

Therefore, implicit data type conversion follows the rules below:

When comparing numeric or character data types or arithmetic operations, the character data types are converted to numeric data types.
When comparing the date data type with the character data type, the character data type is converted to the date data type and the comparison operation is performed.
Operation that cannot convert data types are invalidated.
The argument used in the function is converted to the data type of the argument defined in the function.
If a character data type or numeric data type that uses decimal precisions to a floating-point data type that uses binary precision is converted
When executing an INSERT or UPDATE, the data type is converted to the data type of the INSERT and UPDATE columns.

Example#

<Query> When comparing numeric data types, character data type '10' is converted to numeric data.

iSQL> create table emp (empno integer, name varchar(10), hire_date date);
insert into emp values (10,'altibase', '10-nov-2015');

iSQL> select name from emp where empno = '10';
NAME
--------------
altibase
1 row selected.

<Query> When arithmetic between numeric data type and character data type, character data type '10' is converted to numeric data type.

iSQL> select empno + '10' from emp;
EMPNO+'10'
-------------------------
20
1 row selected.

<Query> When comparing a date date type with a character data type, the character data type '10-nov-2015' is converted to a date data type.

iSQL> select hire_date from emp where hire_date = '10-nov-2015';
HIRE_DATE
---------------
10-NOV-2015
1 row selected.

<Query> When arithmetic between numeric data type and character type is performed, binary data type cannot be converted to numeric data type and operation is invalidated.

iSQL> select empno + cast(12345 as nibble(6)) from emp;
[ERR-2100C : Conversion not applicable.
0001 : select EMPNO + CAST(12345 as NIBBLE(6)) from EMP
             ^                               ^
]

<Query> When the function SUM receives the character data type '10' as an argument, it is converted.

iSQL> select sum('10') from dual;
SUM('10')
--------------
10
1 row selected.

<Query> When the character data type '12.123456789' is converted to a floating-point numeric data type, the number of significant digits becomes float (11), which causes a loss of value.

iSQL> select float'12.123456789' from dual;
FLOAT'12.123456789'
----------------------
12.1234568
1 row selected.

<Query> The value of numeric data to be inserted is converted according to the data type of the column to be inserted and the value is INSERT.

iSQL>  create table t1 ( i1 char(10), i2 integer, i3 double);
Create success.
iSQL>  insert into t1 values (integer'1020', char'1928', float'123.1234');
1 row inserted.
iSQL>     select * from t1;
I1          I2          I3
---------------------------------------------------
1020        1928        123.1234
1 row selected.

Explicit Data Type Conversion#

Data type conversion can be explicitly performed using SQL conversion functions or by type-casting, as shown below. The SQL functions that are used to explicitly convert a value from one data type to another are explained in the SQL Reference.

Syntax#

datatype 'string or constant literal '

Description#

Explicitly converts a numeric value from on data type to another. In the following example, the number 157.27 is converted to the characters "157.27".

CHAR '157.27'

Using the Character Strings#

Single quotation mark should be used when displaying character strings in a SQL query. Since the single quotation mark becomes an escape letter when displaying a single quotation mark ('), the single quotation mark should be used in font.

Example

SELECT * FROM EMPLOYEE WHERE NAME = 'KIM';
INSERT INTO EMPLOYEE VALUES ('GILDONG''');//Insert the value GILDONG'
SELECT * FROM REMOTE_TABLE(link1, 'SELECT * FROM EMPLOYEE WHERE NAME=''KIM'''; //''are not the double quotation mark, it is two single quotation marks

FIXED/VARIABLE Option#

FIXED or VARIABLE specifies where the data in a column will be stored.

When an entire record is stored in a contiguous space, this is called a 'FIXED' area. When one of the columns is stored in a separated space, rather than being stored in the fixed area contiguous with the rest of the record, this column is said to be stored in a 'VARIABLE' area.

When a column is stored in a variable area, the header information for the column, such as the length of the data and the pointer to the actual data, is stored in the fixed area, whereas the data for that column are stored in the variable area.

When a table is created in a disk tablespace, whether the user specifies FIXED or VARIABLE is ignored, and all columns in the table are treated as FIXED. However, when a table is created in a memory table space, the user-specified value is used.

However, the exception to this is that all LOB data type columns are always treated as VARIABLE, and the data can thus be stored in a fixed or variable area depending on the value specified using the IN ROW clause.

The following data types can be specified as VARIABLE:

CHAR, VARCHAR, NCHAR, NVARCHAR, BYTE, VARBATE, NIBBLE, BIT, VARBIT, BLOB, and CLOB

IN ROW Clause#

This clause pertains only to column data that are to be stored in a variable area. If the FIXED and IN ROW clause are both specified when a table is created, the IN ROW clause is ignored. When data are entered into a VARIABLE column, if the length of the data is less than or equal to the value specified using the IN ROW clause, the data will be stored in the fixed area, whereas if the data length is greater than the value specified using the IN ROW clause, the data will be stored in the variable area.

Here, "data length" does not mean the length of the input data, but the length of the data to be stored in memory or on disk, which will be somewhat larger. For example, when a column is defined as "VARCHAR(400) in row 200", data will be inserted into the fixed area if the length of the data that is input is smaller than or equal to 198, because 2 additional bytes are required when storing the data.

The default size of lob data stored in the fixed area can be specified using the MEMORY_LOB_COLUMN_IN_ROW_SIZE property for memory tables and the DISK_LOB_COLUMN_IN_ROW_SIZE for disk tables. Additionally, the default size for columns containing other types of data with the VARIABLE option can be specified using the MEMORY_VARIABLE_COLUMN_IN_ROW_SIZE property.

Setting these properties eliminates the need to use the IN ROW clause repeatedly for each column when creating a table. For more detailed information about these properties, please refer to Chapter 2. Altibase Properties.

Administrator

Reference

Developer

Tools

External Tools