AntDB-Oracle Compatibility Developer's Manual P3–3
Published: 2023-08-30 07:55:00

#### Numeric Types

Numeric types include 4-byte integers, 4-byte and 8-byte floating-point numbers, and fixed-precision decimals. The following table lists the types of values that can be used.


| Name| Storage size| Description| Range
|----------|----------|----------|----------
| INTEGER| 4 bytes| Usually used for integers| -2,147,483,648 to +2,147,483,647
| NUMBER| Variable storage space size| Uses user-specified precision| No limits
| NUMBER(p \[, s ] )| Variable storage space size| Can use exact numeric types in the range s, with a maximum precision of p| Maximum 38 bits
| NUMERIC| | Equivalent to number(38,0)| 
| REAL| | Equivalent to number| 
| FLOAT| | Equivalent to number| 
| DOUBLE PRECISION| | Equivalent to number| 
| BINARY\_DOUBLE| | Equivalent to number| 
| BINARY\_FLOAT| | Equivalent to number| 
| DECIMAL| | Equivalent to number| 



These data types are described in detail in the following sections.

##### Integer types

The INTEGER type is used to store the entire value, with no fractional part of the value. If an attempt is made to store a value outside the allowed range of values, an error will be generated.

##### Numeric values with arbitrary precision

The NUMBER type can be used to store numbers with no restrictions on the number of precision digits, and can be used to perform exact calculations. This type is recommended for storing monetary totals and other types of quantitative values when high precision is required.

The following terminology is used: The range of a NUMBER type value is the number of decimal places to the right of the decimal point. The precision of a value of type NUMBER is all the digits contained in the whole value, that is, all the digits to the right and left of the decimal point. So, we can say that the precision of the value 23.5141 is 6 and the range is 4. We can think of integers as having a range of 0.

The precision and range of the NUMBER type values are configurable. The data type of a column can be declared as NUMBER by using the syntax shown below.

NUMBER(precision, scale)

The precision must be an integer, and the range can be 0 or an integer, which we can also declare using the following method: 

NUMBER(precision)

Specify a range of values to 0. Instead, by specifying the column as a NUMBER type without arbitrary precision and range, you can store values of arbitrary precision and range in the column, where the precision value can reach the upper limit of the practical application. Columns of this type will not force the input value to be converted to any particular range. If the data type of the column is a NUMBER type with a range, then the range of the input value will be forced to be converted to the specified range. (The SQL standard requires the default range value to be 0, e.g., to force the precision of a value to be the precision of an integer, and it is best to explicitly specify the precision and range from the point of view of easiest use.)

If the precision or range of a value is greater than the precision and range declared for the column's data type, then the system will attempt to round the value. If the value cannot be rounded to meet the data type restrictions, an error will be generated.

##### Floating point types

In AntDB, REAL, DOUBLE PRECISION, BINARY\_DOUBLE, BINARY\_FLOAT, DECIMAL, NUMERIC are all implemented with numeric types in postgres, same as number.

```
postgres=# create table test(id int, a number(10,4),b binary_float, c float(4), d binary_double, e double precision, f real, g  decimal(5,3), h numeric);
CREATE TABLE
postgres=# \d+ test
                                      Table "public.test"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------------+-----------+----------+---------+---------+--------------+---------------------
 id     | integer       |           |          |         | plain   |              | 
 a      | numeric(10,4) |           |          |         | main    |              | 
 b      | numeric       |           |          |         | main    |              | 
 c      | numeric       |           |          |         | main    |              | 
 d      | numeric       |           |          |         | main    |              | 
 e      | numeric       |           |          |         | main    |              | 
 f      | numeric       |           |          |         | main    |              | 
 g      | numeric(5,3)  |           |          |         | main    |              | 
 h      | numeric(38,0) |           |          |         | main    |              | 
Access method: heap

```

#### Character types

> The following table shows the character types commonly used in AntDB.

| Name| Description
|----------|----------
| CHAR \[ (n) ]| Fixed length, if the actual data does not reach the specified length, a space will be added to make up for it, n\<=10485760.
| CLOB| Variable length, implemented with text type, stores arbitrary length strings
| VARCHAR2(n)| Variable length type with restrictions, n\<=10485760

CHAR(n) and VARCHAR2(n) are the two most commonly used character types, where n is a positive integer with a maximum value of 10485760. These types can store strings of length up to the length specified by the parameter n. For the CHAR type, if n is not written, then the default value is 1. Attempting to store a string longer than the maximum length in a column will result in an error, unless the characters that exceed the length are spaces, in which case the string will be truncated by the maximum length. If the stored string length is less than the declared length, it will be filled with spaces for CHAR type string values, and stored at the actual length for VARCHAR2 type string values.

If the size of the n value is explicitly specified for VARCHAR2(n) or CHAR(n), then values that exceed the length will be truncated to the length specified by the parameter n without generating an error. (This is also required by the SQL standard)

CHAR-type string values are actually appended with spaces to reach the length specified by the parameter n, and are stored and displayed in this manner. However, the additional spaces are semantically insignificant. Trailing spaces are ignored when comparing two CHAR string values, and are removed when converting a CHAR value to another string type. Note that for VARCHAR2, trailing spaces are semantically meaningful.

The third character type used to store long strings is the CLOB data type. CLOB is semantically equivalent to VARCHAR2 except that there is no limit on the specified length. Generally speaking when the maximum length of a string is not known, it is recommended to use the CLOB type instead of the VARCHAR2 type.

The CLOB type, implemented with the text type, can store a string of any length.

For the storage space requirement of these three types of data, if the string length is less than 127 bytes, then the actual storage space size is the actual string length plus 1 byte, and if the string length is greater than or equal to 127 bytes, then the actual storage space size is the actual string length plus 4 bytes. For CHAR type data, the space appended to the string also needs storage space. The system automatically compresses long strings so that less actual storage space is required on disk. Long string values are stored in background tables, so they do not interfere when accessing short column values quickly.

The database character set determines the character set used to store the text.

#### Binary Data

The BLOB data type allows storing binary strings.

| Name| Storage space| Description
|----------|----------|----------
| BLOB| If the length of the binary string is less than 127 bytes, the actual required length is the length of the binary string plus 1 byte, and if the length of the binary string is greater than or equal to 127 characters, the length is the length of the binary string plus 4 bytes.| Variable length binary strings, up to 2G of data

Blob type data, the result of select is if it is hexadecimal data, the actual inserted data can be displayed by setting the parameter bytea\_output = 'escape'.

```
postgres=#  create table test(id int, a blob);
CREATE TABLE
postgres=# insert into test values(1, 'abcd');
INSERT 0 1
postgres=# select * from test;
 ID |     A      
----+------------
  1 | \x61626364
(1 row)
postgres=# set bytea_output = 'escape';
SET
postgres=#  select * from test;
 ID |  A   
----+------
  1 | abcd
(1 row)
```

A binary string is a sequence of 8-bit bytes (or bytes) - compared to a string, a binary string differs in two ways: First, a binary string allows to store 8-bit bytes with value 0 and other octets that cannot be printed and displayed (defined in the range over 32-126). Second, operations performed on binary strings actually deal with bytes, as opposed to strings whose encoding and processing depends on local settings.

#### Date/time types

AntDB supports the date/time types shown in the following table:

| Name | Storage capacity| Description| Minimum | Maximum| Precision
|----------|----------|----------|----------|----------|----------
| DATE| 8 bytes| Date and time| 4713 B.C.| 294276 A.D.| 1 second
| TIMESTAMP \[ (p) ]| 8 bytes| Date and time| 4173 B.C.| 294276 A.D.| 1 millisecond
| INTERVAL| 16 bytes| Time interval| | | 1 millisecond

Date type underlying the actual storage is the year, month, day, hour, minute and second, if you do not specify the time, it will display the year, month and day 00:00:00.

timestamp(p): we can specify a value p representing precision on the value of the TIMESTAMP type, which is used to specify the number of decimal places that can be retained in the seconds field. The parameter p has a value range between 0 and 6, and the default value is 6. If the user specifies a value of p greater than 6, it will be automatically converted to 6.

Interval: It indicates the time interval, and can be added or subtracted from the time. Compared with Oracle, AntDB does not have full support for Interval, see below.

##### Date/Time input

The general format for date and time input values is ISO 8601 SQL-compatible format, Oracle's default dd-MON-yy format, and some other formats that have a clear distinction between month and year. However, the best way to avoid ambiguity in the format is to use the TO\_DATE function.

Any date or time input value needs to be enclosed in single quotes like a text string, we can use the following SQL standard syntax.

type 'value'

where type can be either a date type or a TIMESTAMP type. value is a string with date/time content.

###### Dates

The following table lists some of the allowed date input formats, all of which have a value equivalent to January 8, 1999.

| Example
|----------
| January 8, 1999
| 01/08/1999
| 1999-Jan-08
| Jan-08-1999
| 08-Jan-1999
| 08-Jan-99
| Jan-08-99
| 19990108
| 990108

We can assign date type values to columns or variables with data type DATE or TIMESTAMP. If the date type value is not accompanied by a time type value, then the field values for hours, minutes and seconds are all zero.

```
create table test(id int, dt date);
insert into test values(1,'January 8, 1999');
insert into test values(2,'1999-01-08');
insert into test values(3,'1999-Jan-08');
insert into test values(4,'Jan-08-1999');
insert into test values(5,'08-Jan-1999');
insert into test values(6,'08-Jan-99');
insert into test values(7,'Jan-08-99');
insert into test values(8,'19990108');
insert into test values(9,'990108');

postgres=# select * from test;
 ID |         DT          
----+---------------------
  1 | 1999-01-08 00:00:00
  2 | 1999-01-08 00:00:00
  3 | 1999-01-08 00:00:00
  4 | 1999-01-08 00:00:00
  5 | 1999-01-08 00:00:00
  6 | 1999-01-08 00:00:00
  7 | 1999-01-08 00:00:00
  8 | 1999-01-08 00:00:00
  9 | 1999-01-08 00:00:00
(9 rows)
```

###### Time

The following table shows some examples of date or timestamp type values with a time component.

| Example| Description
|----------|----------
| 04:05:06.789| ISO 8601
| 04:05:06| ISO 8601
| 04:05:00| ISO 8601
| 040506| ISO 8601
| 04:05 AM| has same meaning as 04:05, AM does not affect the actual value
| 04:05 PM| is same as 16:05; the hour value entered cannot be greater than 12

```
create table test(id int, ts timestamp(6));
insert into test values(1,'1999-01-08 04:05:06.789');
insert into test values(2,'1999-Jan-08 04:05');
insert into test values(3,'January 8, 1999 040506');
insert into test values(4,'19990108 04:05 AM');
insert into test values(5,'08-Jan-99 04:05 PM');
postgres=# select * from test;
 ID |           TS            
----+-------------------------
  1 | 1999-01-08 04:05:06.789
  2 | 1999-01-08 04:05:00
  3 | 1999-01-08 04:05:06
  4 | 1999-01-08 04:05:00
  5 | 1999-01-08 16:05:00
(5 rows)
```