Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

3.2 Data Types

The following table shows the built-in general-purpose data types.

Table 3-3-1 Data Types

Name

Alias

Description

BLOB

LONG RAW, RAW(n)

Binary data

BOOLEAN

 

Logical Boolean (true/false)

CHAR [ (n) ]

CHARACTER [ (n) ]

Fixed-length character string of n characters

CLOB

LONG, LONG VARCHAR

Long character string

DATE

TIMESTAMP(0)

Date and time to the second

DOUBLE PRECISION

FLOAT,

FLOAT(25) – FLOAT(53)

Double precision floating-point number

INTEGER

INT, BINARY_INTEGER, PLS_INTEGER

Signed four-byte integer

NUMBER

DEC, DECIMAL, NUMERIC

Exact numeric with optional decimal places

NUMBER(p [, s ])

DEC(p [, s ]),

DECIMAL(p [, s ]),

NUMERIC(p [, s ])

Exact numeric of maximum precision, p, and optional scale, s

REAL

FLOAT(1) – FLOAT(24)

Single precision floating-point number

TIMESTAMP [ (p) ]

 

Date and time with optional, fractional second precision, p

TIMESTAMP [ (p) ] WITH TIME ZONE

 

Date and time with optional, fractional second precision, p, and with time zone

VARCHAR2(n)

CHAR VARYING(n), CHARACTER VARYING(n), VARCHAR(n)

Variable-length character string with a maximum length of n characters

XMLTYPE

 

XML data

The following sections describe each data type in more detail.

3.2.1 Numeric Types

Numeric types consist of four-byte integers, four-byte and eight-byte floating-point numbers, and fixed-precision decimals. The following table lists the available types.

Table 3-3-2 Numeric Types

Name

Storage Size

Description

Range

INTEGER

4 bytes

Usual choice for integer

-2,147,483,648 to +2,147,483,647

NUMBER

Variable

User-specified precision, exact

Up to 1000 digits of precision

NUMBER(p [, s ] )

Variable

Exact numeric of maximum precision, p, and optional scale, s

Up to 1000 digits of precision

REAL

4 bytes

Variable-precision, inexact

6 decimal digits precision

ROWID

8 bytes

Signed 8 bit integer.

-9223372036854775808 to 9223372036854775807

DOUBLE PRECISION

8 bytes

Variable-precision, inexact

15 decimal digits precision

The following sections describe the types in detail.

3.2.1.1 Integer Types

The type, INTEGER, stores whole numbers (without fractional components) between the values of -2,147,483,648 and +2,147,483,647. Attempts to store values outside of the allowed range will result in an error.

Columns of the ROWID type holds fixed-length binary data that describes the physical address of a record. ROWID is an unsigned, four-byte INTEGER that stores whole numbers (without fractional components) between the values of 0 and 4,294,967,295. Attempts to store values outside of the allowed range will result in an error.

3.2.1.2 Arbitrary Precision Numbers

The type, NUMBER, can store practically an unlimited number of digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, the NUMBER type is very slow compared to the floating-point types described in the next section.

In what follows we use these terms: The scale of a NUMBER is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a NUMBER is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.

Both the precision and the scale of the NUMBER type can be configured. To declare a column of type NUMBER use the syntax

NUMBER(precision, scale)

The precision must be positive, the scale zero or positive. Alternatively,

NUMBER(precision)

selects a scale of 0. Specifying

NUMBER

without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas NUMBER columns with a declared scale will coerce input values to that scale. (The SQL standard requires a default scale of 0, i.e., coercion to integer precision. For maximum portability, it is best to specify the precision and scale explicitly.)

If the precision or scale of a value is greater than the declared precision or scale of a column, the system will attempt to round the value. If the value cannot be rounded so as to satisfy the declared limits, an error is raised.

3.2.1.3 Floating-Point Types

The data types REAL and DOUBLE PRECISION are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and printing back out a value may show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed further here, except for the following points:

If you require exact storage and calculations (such as for monetary amounts), use the NUMBER type instead.

If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.

Comparing two floating-point values for equality may or may not work as expected.

On most platforms, the REAL type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The DOUBLE PRECISION type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding may take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.

Postgres Plus Advanced Server also supports the SQL standard notations FLOAT and FLOAT(p) for specifying inexact numeric types. Here, p specifies the minimum acceptable precision in binary digits. Postgres Plus Advanced Server accepts FLOAT(1) to FLOAT(24) as selecting the REAL type, while FLOAT(25) to FLOAT(53) as selecting DOUBLE PRECISION. Values of p outside the allowed range draw an error. FLOAT with no precision specified is taken to mean DOUBLE PRECISION.

3.2.2 Character Types

The following table shows the general-purpose character types available in Postgres Plus Advanced Server.

Table 3-3-3 Character Types

Name

Description

CHAR [ (n) ]

Fixed-length, blank-padded

CLOB

Large variable-length up to 1 GB

VARCHAR2(n)

Variable-length with limit

The two primary character types are CHAR(n) and VARCHAR2(n), where n is a positive integer. Both of these types can store strings up to n characters in length. In the case of type CHAR, n defaults to 1 if omitted. An attempt to store a longer string into a column of these types will result in an error, unless the excess characters are all spaces, in which case the string will be truncated to the maximum length. If the string to be stored is shorter than the declared length, values of type CHAR will be space-padded; values of type VARCHAR2 will simply store the shorter string.

If one explicitly casts a value to VARCHAR2(n) or CHAR(n), then an over-length value will be truncated to n characters without raising an error. (This too is required by the SQL standard.)

Values of type CHAR are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type CHAR, and they will be removed when converting a CHAR value to one of the other string types. Note that trailing spaces are semantically significant in VARCHAR2 values.

A third character type used for storing large character strings is the CLOB data type. CLOB is semantically equivalent to VARCHAR2 except no length limit is specified. Generally, use CLOB over VARCHAR2 if the maximum string length is not known.

The longest possible character string that can be stored in a CLOB type is about 1 GB.

The storage requirement for data of these three types is the actual string plus 1 byte if the string is less than 127 bytes, or 4 bytes if the string is 127 bytes or greater. In the case of CHAR, the padding also requires storage. Long strings are compressed by the system automatically, so the physical requirement on disk may be less. Long values are also stored in background tables so they do not interfere with rapid access to the shorter column values.

The database character set determines the character set used to store textual values.

3.2.3 Binary Data

The BLOB data type allows storage of binary strings.

Table 3-3-4 Binary Large Object

Name

Storage Size

Description

BLOB

The actual binary string plus 1 byte if the binary string is less than 127 bytes, or 4 bytes if the binary string is 127 bytes or greater.

Variable-length binary string

A binary string is a sequence of octets (or bytes). Binary strings are distinguished from characters strings by two characteristics: First, binary strings specifically allow storing octets of value zero and other "non-printable" octets (defined as octets outside the range 32 to 126). Second, operations on binary strings process the actual bytes, whereas the encoding and processing of character strings depends on locale settings.

3.2.4 Date/Time Types

The following discussion of the date/time types assumes that the configuration parameter, edb_redwood_date, has been set to TRUE whenever a table is created or altered.

Postgres Plus Advanced Server supports the date/time types shown in the following table.

Table 3-3-5 Date/Time Types

Name

Storage Size

Description

Low Value

High Value

Resolution

DATE

8 bytes

Date and time

4713 BC

5874897 AD

1 second

INTERVAL DAY TO SECOND

[(p)]

12 bytes

Period of time

-178000000 years

178000000 years

1 microsecond / 14 digits

INTERVAL YEAR TO MONTH

12 bytes

Period of time

-178000000 years

178000000 years

1 microsecond / 14 digits

TIMESTAMP [(p)]

8 bytes

Date and time

4713 BC

5874897 AD

1 microsecond

TIMESTAMP [(p)] WITH TIME ZONE

8 bytes

Date and time with time zone

4713 BC

5874897 AD

1 microsecond

When DATE appears as the data type of a column in the data definition language (DDL) commands, CREATE TABLE or ALTER TABLE, it is translated to TIMESTAMP(0) at the time the table definition is stored in the database. Thus, a time component will also be stored in the column along with the date.

When DATE appears as a data type of a variable in an SPL declaration section, or the data type of a formal parameter in an SPL procedure or an SPL function, or the return type of an SPL function, it is always translated to TIMESTAMP(0) and thus can handle a time component if present.

TIMESTAMP accepts an optional precision value p which specifies the number of fractional digits retained in the seconds field. The allowed range of p is from 0 to 6 with the default being 6.

When TIMESTAMP values are stored as double precision floating-point numbers (currently the default), the effective limit of precision may be less than 6. TIMESTAMP values are stored as seconds before or after midnight 2000-01-01. Microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. When TIMESTAMP values are stored as eight-byte integers (a compile-time option), microsecond precision is available over the full range of values. However eight-byte integer timestamps have a more limited range of dates than shown above: from 4713 BC up to 294276 AD.

TIMESTAMP (p) WITH TIME ZONE is similar to TIMESTAMP (p), but includes the time zone as well.

3.2.4.1 INTERVAL Types

INTERVAL values specify a period of time. Values of INTERVAL type are composed of fields that describe the value of the data. The following table lists the fields allowed in an INTERVAL type:

Field Name

INTERVAL Values Allowed

YEAR

Integer value (positive or negative)

MONTH

0 through 11

DAY

Integer value (positive or negative)

HOUR

0 through 23

MINUTE

0 through 59

SECOND

0 through 59.9(p) where 9(p) is the precision of fractional seconds

The fields must be presented in descending order – from YEARS to MONTHS, and from DAYS to HOURS, MINUTES and then SECONDS.

Advanced Server supports two Oracle-compatible INTERVAL types.

The first variation supported by Advanced Server is INTERVAL DAY TO SECOND [(p)]. INTERVAL DAY TO SECOND [(p)] stores a time interval in days, hours, minutes and seconds.

      p specifies the precision of the second field.

Advanced Server interprets the value:

      INTERVAL '1 2:34:5.678' DAY TO SECOND(3)

as 1 day, 2 hours, 34 minutes, 5 seconds and 678 thousandths of a second.

Advanced Server interprets the value:

      INTERVAL '1 23' DAY TO HOUR

as 1 day and 23 hours.

Advanced Server interprets the value:

      INTERVAL '2:34' HOUR TO MINUTE

as 2 hours and 34 minutes.

Advanced Server interprets the value:

      INTERVAL '2:34:56.129' HOUR TO SECOND(2)

as 2 hours, 34 minutes, 56 seconds and 13 thousandths of a second. Note that the fractional second is rounded up to 13 because of the specified precision.

The second Oracle-compatible variation supported by Advanced Server is INTERVAL YEAR TO MONTH. This variation stores a time interval in years and months.

Advanced Server interprets the value:

      INTERVAL '12-3' YEAR TO MONTH

as 12 years and 3 months.

Advanced Server interprets the value:

      INTERVAL '456' YEAR(2)

as 12 years and 3 months.

Advanced Server interprets the value:

      INTERVAL '300' MONTH

as 25 years.

3.2.4.2 Date/Time Input

Date and time input is accepted in ISO 8601 SQL-compatible format, the Oracle default dd-MON-yy format, as well as a number of other formats provided that there is no ambiguity as to which component is the year, month, and day. However, use of the TO_DATE function is strongly recommended to avoid ambiguities. See Section 3.5.6.

Any date or time literal input needs to be enclosed in single quotes, like text strings. The following SQL standard syntax is also accepted:

type 'value'

type is either DATE or TIMESTAMP.

value is a date/time text string.

3.2.4.2.1 Dates

The following table shows some possible input formats for dates, all of which equate to January 8, 1999.

Table 3-3-6 Date Input

Example

January 8, 1999

1999-01-08

1999-Jan-08

Jan-08-1999

08-Jan-1999

08-Jan-99

Jan-08-99

19990108

990108

The date values can be assigned to a DATE or TIMESTAMP column or variable. The hour, minute, and seconds fields will be set to zero if the date value is not appended with a time value.

3.2.4.2.2 Times

Some examples of the time component of a date or time stamp are shown in the following table.

Table 3-3-7 Time Input

Example

Description

04:05:06.789

ISO 8601

04:05:06

ISO 8601

04:05

ISO 8601

040506

ISO 8601

04:05 AM

Same as 04:05; AM does not affect value

04:05 PM

Same as 16:05; input hour must be <= 12

3.2.4.2.3 Time Stamps

Valid input for time stamps consists of a concatenation of a date and a time. The date portion of the time stamp can be formatted according to any of the examples shown in Table 3-3-6 Date Input. The time portion of the time stamp can be formatted according to any of examples shown in Table 3-3-7 Time Input.

The following is an example of a time stamp which follows the Oracle default format.

08-JAN-99 04:05:06

The following is an example of a time stamp which follows the ISO 8601 standard.

1999-01-08 04:05:06

3.2.4.3 Date/Time Output

The default output format of the date/time types will be either the Oracle compatible style (dd-MON-yy) referred to as the Redwood date style, or the ISO 8601 format (yyyy-mm-dd) depending upon the application interface to the database. Applications that use JDBC such as SQL Interactive always present the date in ISO 8601 form. Other applications such as PSQL present the date in Redwood form.

The following table shows examples of the output formats for the two styles, Redwood and ISO 8601.

Table 3-3-8 Date/Time Output Styles

Description

Example

Redwood style

31-DEC-05 07:37:16

ISO 8601/SQL standard

1997-12-17 07:37:16

3.2.4.4 Internals

Postgres Plus Advanced Server uses Julian dates for all date/time calculations. Julian dates correctly predict or calculate any date after 4713 BC based on the assumption that the length of the year is 365.2425 days.

3.2.5 Boolean Type

Postgres Plus Advanced Server provides the standard SQL type BOOLEAN. BOOLEAN can have one of only two states: TRUE or FALSE. A third state, UNKNOWN, is represented by the SQL NULL value.

Table 3-3-9 Boolean Type

Name

Storage Size

Description

BOOLEAN

1 byte

Logical Boolean (true/false)

The valid literal value for representing the true state is TRUE. The valid literal for representing the false state is FALSE.

3.2.6 XML Type

The XMLTYPE data type is used to store XML data. Its advantage over storing XML data in a character field is that it checks the input values for well-formedness, and there are support functions to perform type-safe operations on it.

The XML type can store well-formed “documents”, as defined by the XML standard, as well as “content” fragments, which are defined by the production XMLDecl? content in the XML standard. Roughly, this means that content fragments can have more than one top-level element or character node.

Note: Oracle does not support the storage of content fragments in XMLTYPE columns.

The following example shows the creation and insertion of a row into a table with an XMLTYPE column.

CREATE TABLE books (
    content         XMLTYPE
);

INSERT INTO books VALUES (XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>'));

SELECT * FROM books;

                         content
----------------------------------------------------------
 <book><title>Manual</title><chapter>...</chapter></book>
(1 row)

Previous PageTable Of ContentsNext Page