The following data types are supported:
VARCHAR2
NVARCHAR2
DATE
RAW
PLS_INTEGER
BINARY_FLOAT
BINARY_DOUBLE
XMLType
LONG
Syntax
Specify the VARCHAR2 type as follows.
Table 9.26. VARCHAR2 Syntax
|
Data type syntax |
Explanation |
|---|---|
|
VARCHAR2(len [byte|char]) |
String with a variable length up to len characters. For len, specify an integer greater than 0. If len is omitted, the string can be any length. Since 23.1, LightDB implements this syntax-sugar for compatibility with oracle varchar2 types. The keyword byte or char after len is optional, it is only compatitable for oracle syntax, internal implementation is the same as only len without byte or char keyword. |
General rules
VARCHAR2 is a character data type. Specify the number of characters for the length.
Strings are of variable length. The specified value will be stored as is. The upper limit for this data type is approximately one gigabyte.
Note
The VARCHAR2 type does not support collating sequences. Therefore, the following error occurs when a collating sequence like that of an ORDER BY clause is required. At this time, the following HINT will prompt to use a COLLATE clause, however, because collating sequences are not supported, it is not possible to use this clause.
ERROR: could not determine which collation to use for string comparison
HINT: Use the COLLATE clause to set the collation explicitly.
If the error shown above is displayed, explicitly cast the column to VARCHAR or TEXT type.
Example
lightdb@postgres=# create table t1(name varchar2(10));
CREATE TABLE
lightdb@postgres=#
lightdb@postgres=# \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------+-----------+----------+---------+---------+--------------+-------------
name | varchar2(10) | | | | plain | |
Access method: heap
lightdb@postgres=#
lightdb@postgres=# create table t2(name varchar2(10 byte));
CREATE TABLE
lightdb@postgres=#
lightdb@postgres=# \d+ t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------+-----------+----------+---------+---------+--------------+-------------
name | varchar2(10) | | | | plain | |
Access method: heap
lightdb@postgres=#
lightdb@postgres=# create table t3(name varchar2(10 char));
CREATE TABLE
lightdb@postgres=#
lightdb@postgres=# \d+ t3
Table "public.t3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------+-----------+----------+---------+---------+--------------+-------------
name | varchar2(10) | | | | plain | |
Access method: heap
lightdb@postgres=#
Syntax
Specify the NVARCHAR2 type as follows.
Table 9.27. NVARCHAR2 Syntax
|
Data type syntax |
Explanation |
|---|---|
|
NVARCHAR2(len) |
National character string with a variable length up to len characters. For len, specify an integer greater than 0. If len is omitted, the string can be any length. |
General rules
NVARCHAR2 is a national character data type. Specify the number of characters for the length.
Strings are of variable length. The specified value will be stored as is. The upper limit for this data type is approximately one gigabyte.
Note
The NVARCHAR2 type does not support collating sequences. Therefore, the following error occurs when a collating sequence like that of an ORDER BY clause is required. At this time, the following HINT will prompt to use a COLLATE clause, however, because collating sequences are not supported, it is not possible to use this clause.
ERROR: could not determine which collation to use for string comparison
HINT: Use the COLLATE clause to set the collation explicitly.
If the error shown above is displayed, explicitly cast the column to NCHAR VARYING or TEXT type.
Syntax
Specify the DATE type as follows.
Table 9.28. DATE Syntax
|
Data type syntax |
Explanation |
|---|---|
|
DATE |
Stores date and time |
General rules
DATE is a date/time data type.
Date and time are stored in DATE. The time zone is not stored.
Note
If the DATE type of orafce is used in DDL statements such as table definitions, always set search_path before executing a DDL statement. Even if search_path is changed after definition, the data type will be the DATE type of LightDB.
Information
The DATE type of orafce is equivalent to the TIMESTAMP type of LightDB. Therefore, of the existing functions of LightDB, functions for which the data type of the argument is TIMESTAMP can be used.
Syntax
Specify the RAW type as follows.
Table 9.29. RAW Syntax
|
RAW type syntax |
Explanation |
|---|---|
|
RAW(len) |
Store variable-length Binary data. External representation is hexadecimal. The len is only used to be compatible with oracle, it has no effect. the binary data can be any length. |
General rules
RAW is a variable-length hex data type.
Hex strings are of variable length. The specified value will be stored as binary data. The upper limit for this data type is approximately one gigabyte.
Syntax
Specify the PLS_INTEGER type as follows.
Table 9.30. PLS_INTEGER Syntax
|
PLS_INTEGER type syntax |
Explanation |
|---|---|
|
PLS_INTEGER |
The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits. |
General rules
PLS_INTEGER behaves like int4.
Syntax
Specify the BINARY_FLOAT type as follows.
Table 9.31. BINARY_FLOAT Syntax
|
BINARY_FLOAT type syntax |
Explanation |
|---|---|
|
BINARY_FLOAT |
BINARY_FLOAT is a 32-bit, single-precision floating-point number data type. Each BINARY_FLOAT value requires 4 bytes. |
General rules
BINARY_FLOAT behaves like float4 in lightdb.
The same as oracle, it follows the IEEE754 standard.
Example
lightdb@test_o=# create table t1(key1 binary_float);
CREATE TABLE
lightdb@test_o=# \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Sta
ts target | Description
--------+---------------+-----------+----------+---------+---------+----
----------+-------------
key1 | binary_float | | | | plain |
|
Access method: heap
lightdb@test_o=# insert into t1 values(123456789012345678901234567890123456789);
INSERT 0 1
lightdb@test_o=# insert into t1 values('infinity');
INSERT 0 1
lightdb@test_o=# insert into t1 values('NaN');
INSERT 0 1
lightdb@test_o=# select * from t1;
key1
---------------
1.2345679e+38
Infinity
NaN
(3 rows)
Syntax
Specify the BINARY_DOUBLE type as follows.
Table 9.32. BINARY_DOUBLE Syntax
|
BINARY_DOUBLE type syntax |
Explanation |
|---|---|
|
BINARY_DOUBLE |
BINARY_DOUBLE is a 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes. |
General rules
BINARY_DOUBLE behaves like float8 in lightdb.
The same as oracle, it follows the IEEE754 standard.
Example
lightdb@test_o=# create table t1(key1 binary_double);
CREATE TABLE
lightdb@test_o=# \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Sta
ts target | Description
--------+---------------+-----------+----------+---------+---------+----
----------+-------------
key1 | binary_double | | | | plain |
|
Access method: heap
lightdb@test_o=# insert into t1 values(123456789012345678901234567890123456789);
INSERT 0 1
lightdb@test_o=# insert into t1 values('infinity');
INSERT 0 1
lightdb@test_o=# insert into t1 values('NaN');
INSERT 0 1
lightdb@test_o=# select * from t1;
key1
------------------------
1.2345678901234568e+38
Infinity
NaN
(3 rows)
Syntax
Specify the XMLType type as follows.
Table 9.33. XMLType Syntax
|
XMLType syntax |
Explanation |
|---|---|
|
XMLType |
XMLType is an object type, that can be used to store XML data. |
General rules
XMLType is xml data type.
Implicit conversions between oracle.XMLType and pg_catalog.xml are supported.
member function
function getClobVal() return CLOB
returns XML as text
Example
lightdb@postgres=# create database test_oracle lightdb_syntax_compatible_type oracle;
CREATE DATABASE
lightdb@postgres=# \c test_oracle
You are now connected to database "test_oracle" as user "lightdb".
compatible type: oracle
lightdb@test_oracle=# select dbms_output.serveroutput(true);
serveroutput
--------------
(1 row)
lightdb@test_oracle=# CREATE TABLE xml_table (id NUMBER, xml_column XMLType );
CREATE TABLE
lightdb@test_oracle=# INSERT INTO xml_table (id, xml_column) VALUES (1, XMLType('<root><name>John</name></root>'));
INSERT 0 1
lightdb@test_oracle=# DECLARE
lightdb@test_oracle$# v_clob CLOB;
lightdb@test_oracle$# BEGIN
lightdb@test_oracle$# SELECT XMLType.getClobVal(xml_column)
lightdb@test_oracle$# INTO v_clob
lightdb@test_oracle$# FROM xml_table
lightdb@test_oracle$# WHERE id = 1;
lightdb@test_oracle$# DBMS_OUTPUT.PUT_LINE('CLOB value: ' || v_clob);
lightdb@test_oracle$# END;
lightdb@test_oracle$# /
CLOB value: <root><name>John</name></root>
DO
lightdb@test_oracle=# DECLARE
lightdb@test_oracle$# xml_data XMLType;
lightdb@test_oracle$# clob_data CLOB;
lightdb@test_oracle$# BEGIN
lightdb@test_oracle$# xml_data := XMLType('<root><name>John Doe</name></root>');
lightdb@test_oracle$# clob_data := xml_data.getClobVal();
lightdb@test_oracle$# DBMS_OUTPUT.PUT_LINE(clob_data);
lightdb@test_oracle$# END;
lightdb@test_oracle$# /
<root><name>John Doe</name></root>
DO
lightdb@test_oracle=# SELECT XMLType.getClobVal(xml_column) from xml_table;
getclobval
--------------------------------
<root><name>John</name></root>
(1 row)