Understanding SQL Data Types

This chapter describes SQL data types, along with data type storage formats, literals, and SQL constants.

SQL Data Types

The following table lists the SQL data types:

Character Data Types

Character data types are strings of characters. Upper and lower case alphabetic characters are accepted literally. There are two fixed-length character data types: char and c, and three variable-length character data types: varchar, long varchar, and text.

The maximum row length is dependent on the default_page_size setting (a DBMS Server configuration parameter), and can be set to a maximum of 32,767 bytes. For further information on page and row size configuration, see the Database Administrator Guide.

The maximum length of a character column is limited by the maximum row width configured, but cannot exceed 32,000. Long varchar columns are an exception: the maximum length of these columns is 2 GB.

C Data Types

Fixed-length c data types accept only printing characters. Non-printing characters, such as control characters, are converted into blanks.

Blanks are ignored when c strings are compared. For example, this c string:

'the house is around the corner'

is considered equal to:

'thehouseisaroundthecorner'

Note: C is supported for backward compatibility; instead, char is the preferred fixed length character type.

Char Data Types

Fixed-length char strings can contain any printing or non-printing character, and the null character ('\0'). In uncompressed tables, char strings are padded with blanks to the declared length. (If the column is nullable, char columns require an additional byte of storage.) For example, if ABC is entered into a char(5) column, five bytes are stored, as follows:

'ABC  '

Leading and embedded blanks are significant when comparing char strings. For example, the following char strings are considered different:

'A B C'
'ABC'

When selecting char strings using the underscore (_) wildcard character of the like predicate, include any trailing blanks to be matched. For example, to select the following char string:

'ABC '

the wildcard specification must also contain trailing blanks:

'_____'

Length is not significant when comparing char strings; the shorter string is (logically) padded to the length of the longer. For example, the following char strings are considered equal:

'ABC'
'ABC '

Note: Character is a synonym for char.

Text Data Types

All ASCII characters except the null character (\0) are allowed within text strings. Null characters are converted to blanks.

Blanks are not ignored when text strings are compared. Unlike varchar, if the strings are unequal in length, blanks are not added to the shorter string. For example, assume that the following text strings are being compared:

'abcd'

and

'abcd '

The string 'abcd  ' is considered greater than the string 'abcd' because it is longer.

Note: Text is supported for backward compatibility; instead, varchar is the preferred varying length character type.

Varchar Data Types

Varchar strings are variable-length strings, stored as a 2-byte (smallint) length specifier followed by data. In uncompressed tables, varchar columns occupy their declared length. For example, if ABC is entered into a varchar(5) column, the stored result is:

'03ABCxx'

where:

03 is a 2-byte length specifier

ABC is three bytes of data

xx represents two bytes containing unknown (and irrelevant) data.

If the column is nullable, varchar columns require an additional byte of storage.

In compressed tables, varchar columns are stripped of trailing data. For example, if "ABC" is entered into a varchar(5) column in a compressed table, the stored result is:

'03ABC'

The varchar data type can contain any character, including non-printing characters and the ASCII null character ('\0').

Blanks are significant in the varchar data type. For example, the following two varchar strings are not considered equal:

'the store is closed'

and

'thestoreisclosed'

If the strings being compared are unequal in length, the shorter string is padded with trailing blanks until it equals the length of the longer string.

For example, consider the following two strings:

'abcd\001'

where:

'\001' represents one ASCII character (ControlA)

and

'abcd'

If they are compared as varchar data types, then

'abcd' > 'abcd\001'

because the blank character added to 'abcd' to make the strings the same length has a higher value than ControlA ('\040' is greater than '\001').

Long Varchar Data Types

The long varchar data type has the same characteristics as the varchar data type, but can accommodate strings up to 2 GB in length.

Do not declare a length for long varchar columns. In embedded SQL, data handlers can be created, which are routines to read and write the data for long varchar (and long byte) columns. For more information on data handlers, see Data Handlers for Large Objects and the Embedded SQL Companion Guide.

Restrictions on Long Varchar Columns

The following restrictions apply to long varchar columns:

• They cannot be part of a table key.
• They do not declare a length.
• They cannot be part of a secondary index.
• They cannot be used in the order by or group by clause in a Select SQL statement.
• They can be included in a select list with the "distinct" qualifier, but duplicate values will not be eliminated.
• They cannot have query optimization statistics. For details about query optimization statistics, see the discussion of the optimizedb utility in the Command Reference Guide.
• The following string functions do not work with long varchar columns:
  • Locate
  • Pad
  • Shift
  • Squeeze
  • Trim
  • Notrim
  • Charextract

    These columns cannot be directly compared to other string data types. To compare a long varchar column to another string data type, apply a coercion function.

    A string literal of more than 2000 characters cannot be assigned to a long varchar column. Details about assigning long strings to these columns are found in the description of data handlers in the Embedded SQL Companion Guide or the OpenAPI User Guide.

Unicode Data Types

Unicode data types nchar, nvarchar and long nvarchar are used to store Unicode data. They behave similar to char, varchar and long varchar character types respectively, except that each character in Unicode types typically uses 16 bits. Similar to their local character counterparts, nchar types are of fixed length and nvarchar and long nvarchar are of variable length.

Ingres represents Unicode data in UTF-16 encoding form and internally stores them in Normalization Form D (NFD) or Normalization Form C (NFC) depending upon the createdb flag (-n or –i) used for creating the database. Each character of a Unicode value is typically stored in a 2-byte code point (some complex characters require more). The maximum length of a Unicode column is limited by the maximum row width configured, but cannot exceed 16,000 characters for nchar and 15,999 for nvarchar. Long nvarchar columns can have a maximum length of 2 GB.

Unicode data types support the coercion of local character data to Unicode data, and of Unicode data to local character data. Coercion function parameters are valid character data types (for example, char, c, varchar and long varchar) and valid Unicode data types (nchar, nvarchar, and long nvarchar.).

Embedded programs use wchar_t data type to store and process Unicode values.

Note: No matter what size the compilation platform uses for the data type wchar_t, Ingres initializes only the low 16 bits with UTF-16 data. When Ingres reads values from wchar_t variables, the values are coerced to 16 bits and stored in the NFD or NFC canonical form. Applications that make use of any available bits beyond the lower 16 to represent information, for example for UTF-32, will not be able to store that information directly in Ingres. It is the responsibility of the application to convert UTF-32 encoded Unicode to UTF-16 encoded Unicode for use with the Ingres Unicode data types.

For details on Unicode Normalization Forms, go to http://www.unicode.org.

Numeric Data Types

There are two categories of numeric data types: exact and approximate. Exact data types include integer data types and decimal data types. Approximate data types include floating point data types.

Integer Data Types

Exact numeric data types includes the following integer data types:

• tinyint (one-byte)
• smallint (two-byte)
• integer (four-byte)
• bigint (eight-byte)

The following table lists the ranges of values for each integer data type:

Decimal Data Types

The decimal data type is an exact numeric data type defined in terms of its precision (total number of digits) and scale (number of digits to the right of the decimal point).

The following is an example of precision and scale in decimal values:

The minimum precision for a decimal value is 1 and the maximum precision is 31. The scale of a decimal value cannot exceed its precision. Scale can be 0 (no digits to the right of the decimal point).

Note: The decimal data type is suitable for storing currency data where the required range of values or number of digits to the right of the decimal point exceeds the capacities of the money data type. Note that, for display purposes, a currency sign cannot be specified for decimal values.

Decimal Data Type Syntax

Specify the decimal data type using the following syntax:

decimal(p,s)

• p

  Defines the precision.

• s

  Defines the scale.

Note: Valid synonyms for the decimal data type are dec and numeric.

Floating Point Data Types

A floating point value is represented either as whole plus fractional digits (like decimal values) or as a mantissa plus an exponent. The following is an example of the mantissa and exponent parts of floating point values:

There are two floating point data types:

• float4 (4-byte)
• float (8-byte)

Note: Real is a synonym for float4, while float8 and double precision are synonyms for float.

Floating point numbers are stored in four or eight bytes. Internally, Ingres rounds eight byte numbers to 15 decimal digits. The precision of four byte numbers is processor dependent.

You can specify the binary precision (number of significant bits) for a floating point value using the following optional syntax:

fload(n)

where n is a value from 0 to 53. Storage is allocated according to the precision that is specified, as follows:

Float Point Limitations

Users must consider the effects of data type conversions when numeric values are combined or compared. This is especially true when dealing with floating point values.

Exact matches on floating point numbers are discouraged, because float and float4 data types are approximate numeric values. Integer and decimal data types, on the other hand, are exact numeric values.

Date/Time Data Types

Date/time data types include the following:

• Date data types
• Time data types
• Timestamp data types
• Interval data types

Date/Time Input Formats

Date/time data values have the following data type input formats:

• Ansidate
• Ingresdate
• Time (without time zone)
• Time with time zone
• Time with local time zone
• Timestamp (without time zone)
• Timestamp with time zone
• Timestamp with local time zone
• Interval year to month
• Interval day to second

Date Data Type

The declaration format of the date type can be one of the following:

DATE

ANSIDATE

INGRESDATE

Examples:

Time Data Types

The TIME data types include the following:

• Time or time without time zone
• Time with time zone
• Time with local time zone

The format is as follows:

TIME [time_precision] [time_zone_spec]

• time_precision

  (Optional) Indicates the number of digits of precision in the fractions of seconds, as an integer value from 0 to 9. When no time precision is supplied, the value of time_precision is set to 0 by default.

• time_zone_spec

  (Optional) Specifies a time zone as one of the following:

  • WITH TIME ZONE
  • WITHOUT TIME ZONE
  • WITH LOCAL TIME ZONE

Example:

Note: When a value is entered that exceeds the specified precision, the value is truncated at the specified precision.

Note: The time value in TIME WITH TIME ZONE data type indicates the time at the specified time zone.

Timestamp Data Types

The TIMESTAMP data type consists of a date and time.

The TIMESTAMP data types include the following:

• Timestamp or timestamp without time zone
• Timestamp with time zone
• Timestamp with local time zone

The format is as follows:

timestamp [(timestamp_precision)] [time_zone_spec]

• timestamp_precision

  (Optional) Indicates the number of digits of precision in the fractions of seconds, as an integer value from 0 to 9. When no time precision is supplied, the value of time_precision is set to 6 by default.

• time_zone_spec

  (Optional) Specifies a time zone as one of the following:

  • WITH TIME ZONE
  • WITHOUT TIME ZONE
  • WITH LOCAL TIME ZONE

Example:

Note: Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the database. For example:

TIMESTAMP '2006-12-15 9:30:55 -8:00'

is the same as

TIMESTAMP '2006-12-15 12:30:55 -5:00'

Note: When a value is entered that exceeds the specified precision, the value is truncated at the specified precision.

Note: The time value in TIMESTAMP WITH TIME ZONE data type indicates the time at the specified time zone.

Interval Data Types

The INTERVAL data types include the following:

• Interval year to month
• Interval day to second

The format is as follows:

interval interval_qualifier

• interval_qualifier

  Defines an interval column as one of the following:

  • YEAR TO MONTH
  • DAY TO SECOND [(second precision)]

    where second precision is the number of digits in the fractions of seconds field.

Example:

Note: When a value is entered that exceeds the specified precision, the value is truncated at the specified precision.

Summary of ANSI Date/Type Data Types

The following table summarizes valid input and output formats for ANSI date/time data types:

where:

• yyyy

  Is a four-digit year specification. All four digits are required.

• mm

  Is a two-digit month specification between 01 to 12.

• dd

  Is a two-digit day specification between 01 to 31.

• hh

  Is a two-digit hour between 00 to 23.

• mm

  Is a two-digit minute between 00 to 59.

• th

  Is a two-digit hour component between -12 to +14.

• tm

  Is a two-digit minute component between 00 to 59.

• Years

  Is the number of years.

• Days

  Is the number of days.

• SS.fffff…

  Is a two-digit seconds value, which can be followed by 0 to 9 digits of fractional seconds.

Ingresdate Data Types

The ingresdate data type is an abstract data type. Ingresdate values can contain either absolute dates and times or time intervals. There are six ingresdate data type input formats:

• Absolute date input
• Absolute time input
• Combined date and time input
• Date interval
• Time interval

Absolute Date Input

Ingresdate values are specified as quoted character strings. A date can be entered by itself or together with a time value. If a date is entered without specifying the time, no time is shown when the data displays.

II_DATE_FORMAT for Ingresdate

The legal formats for absolute ingresdate values are determined by the II_DATE_FORMAT setting, summarized in the following table. If it is not set, the US formats are the default input formats. II_DATE_FORMAT can be set on a session basis. For information on setting II_DATE_FORMAT, see the System Administrator Guide.

The following table lists date input formats:

Year defaults to the current year. In formats that include delimiters (such as forward slashes or dashes), specify the last two digits of the year; the first two digits default to the current century (2000). For example, if this date is entered:

'03/21/03'

using the format mm/dd/yyyy, the DBMS Server assumes that you are referring to March 21, 2003.

In three-character month formats, for example, dd-mmm-yy, specify three-letter abbreviations for the month (for example, mar, apr, may).

To specify the current system date, use the constant, today. For example:

select date('today');

To specify the current system date and time, use the constant, now.

II_DATE_CENTURY_BOUNDARY

The II_DATE_CENTURY_BOUNDARY variable, which can be set to an integer in the 0< n <=100 range, dictates the implied century for an ingresdate value when only the last two digits of the year are entered.

For example, if II_DATE_CENTURY_BOUNDARY is 50 and the current year is 1999, an input date of 3/17/51 is treated as March 17, 1951, but a date of 03/17/49 is treated as March 17, 2049.

If the II_DATE_CENTURY_BOUNDARY variable is not set or if it is set to 0 or 100, the current century is used. If the user enters the full four digits for the year in a four-digit year field in the application, the year is accepted as entered, regardless of the II_DATE_CENTURY_BOUNDARY setting.

Absolute Time Input

The legal format for inputting an absolute time into an ingresdate value is:

'hh:mm[:ss] [am|pm] [timezone]'

Input formats for absolute times are assumed to be on a 24-hour clock. If a time with the designation am or pm is entered, the time is converted to a 24-hour internal and displayed representation.

If timezone is omitted, the local time zone designation is assumed. Times are stored as Greenwich Mean Time (GMT) and displayed using the time zone adjustment specified by II_TIMEZONE_NAME.

If an absolute time without a date is entered, the date defaults to the current system date.

Combined Date and Time Input

Any valid absolute date input format can be paired with a valid absolute time input format to form a valid date and time entry in an ingresdate. The following table shows examples of valid date and time entries, using the US absolute date input formats:

Date Interval

Ingresdate interval values, like absolute date values, are entered as quoted character strings. Date intervals can be specified in terms of years, months, days, or combinations of these. Years and months can be abbreviated to yrs and mos, respectively. For example:

'5 years'
'8 months'
'14 days'
'5 yrs 8 mos 14 days'
'5 years 8 months'
'5 years 14 days'
'8 months 14 days'

The following table lists valid ranges for date intervals:

Time Interval

Time intervals can be specified as hours, minutes, seconds, or combinations of these units. They can be abbreviated to hrs, mins, or secs. For example:

'23 hours'
'38 minutes'
'53 seconds'
'23 hrs 38 mins 53 secs'
'23 hrs 53 seconds'
'28 hrs 38 mins'
'38 mins 53 secs'
'23:38 hours'
'23:38:53 hours'

All values in an interval must be in the range -2,147,483,639 to +2,147,483,639. If a time interval greater than 1 day is entered, the interval is converted to a date and time interval.

For example:

'26 hours'

is converted to:

'1 day 2 hours'

Date and Time Display

Ingresdate values display as strings of 25 characters with trailing blanks inserted.

To specify the output format of an absolute date and time, set II_DATE_FORMAT. For a list of II_DATE_FORMAT settings and associated formats, see Absolute Date Input. The display format for absolute time is:

hh:mm:ss

The DBMS Server displays 24-hour times for the current time zone, which is determined when Ingres is installed. Dates are stored in Greenwich Mean Time (GMT) and adjusted for your time zone when they are displayed.

If seconds are not entered when entering a time, zeros display in the seconds place.

For a time interval, Ingres displays the most significant portions of the interval that fit in the 25-character string. If necessary, trailing blanks are appended to fill out the string. The format appears as:

yy yrs mm mos dd days hh hrs mm mins ss secs

Significance is a function of the size of any component of the time interval. For instance, if the following time interval is entered:

5 yrs 4 mos 3 days 12 hrs 32 min 14 secs

the entry is displayed as:

5 yrs 4 mos 3 days 12 hrs

Truncating the least significant portion of the time (the minutes and seconds) to fit the result into 25 characters.

Coercion Between Date/Time Data Types

The rules governing coercion between the various date/time data types are as follows:

1. Ansidate cannot be converted to any of the time types, nor can the time types be converted to ansidate. Doing so results in error E_AD5066_DATE_COERCION.
2. When converting from a data type that does not have time zone information to a data type with time zone value (for example, ansidate to a timestamp with time zone, time without time zone to a time with time zone), the time zone is set to the current session time zone.

   Example: In Easter Standard Time (EST) time zone (that is, -05:00), the following statements insert a value of 2007-02-08 16:41:00-05:00 in the database.

   create table tab (col1 timestamp with time zone);

   insert into tab values (TIMESTAMP '2007-02-08 16:41:00');

3. Ingresdate, time with local time zone, and timestamp with local time zone, store date/time values in UTC. When converting from other data types like ansidate, time with/without time zone, timestamp with/without time zone to these data types, the session time zone displacement is subtracted from the date/time value. On the reverse operation, when converting from ansidate, time with/without time zone, timestamp with/without time zone to ingresdate, time with local time zone and timestamp with local time zone, the session time zone displacement is added to the date/time value in the database to make it in local time zone.

   Example: In EST time zone (with time zone displacement of -05 :00), the following query stores a value of 2007-02-18 15:04:12 in the database:

   create table tab (col1 timestamp with local time zone);

   insert into tab values (TIMESTAMP '2007-02-18 10:04:12');

   If this value was selected in PST time zone (with time zone displacement of -08:00), the session time zone value is added to the value stored in database and the value in local time zone is displayed, that is:

   2007-02-18 07:04:12

4. When a time value is converted to a timestamp, date, or time/timestamp with local time zone types, the year, month, and day fields are filled with the current year, month, and day value.

   Example: If current date is 08 Feb 2007 then the following statements insert a value of 2007-02-08 17:01:00 in the database:

   create table tab (col1 timestamp);

   insert into tab values (TIME '17:01:00');

5. When converting from time without time zone to time with local time zone, the following procedure is used:
   1. Current date is added to the time value to make a timestamp.
   2. Time zone displacement is then applied to the time value.
   3. The date part is removed from the result.

      Example: If current date is 08 Feb 2007 and the session time zone is -05 :00 (EST), the following query stores a value of 22:01:00 in the database:

      create table tab (col1 time with local time zone);

      insert into tab values (TIME '17:01:00');

6. INTERVAL types cannot be converted to any other types except themselves and ingresdates.
7. When a time/timestamp with time zone is converted to ingresdate, time with local time zone, or timestamp with local time zone, the time value is converted to UTC by applying the time zone information in the value.

   Example: In any time zone, the following query will insert a value of 2007-02-18 03:04:12 in the database:

   create table tab (col1 timestamp with local time zone);

   insert into tab values (TIMESTAMP '2007-02-18 10:04:12-07:00');

Abstract Data Types

Abstract data types include the following:

• Money data types
• Logical key data types

Money Data Types

The money data type is an abstract data type. Money values are stored significant to two decimal places. These values are rounded to their amounts in dollars and cents or other currency units on input and output, and arithmetic operations on the money data type retain two-decimal-place precision.

Money columns can accommodate the following range of values:

$-999,999,999,999.99 to $999,999,999,999.99

A money value can be specified as either:

• A character string literal—The format for character string input of a money value is $sdddddddddddd.dd. The dollar sign is optional and the algebraic sign(s) defaults to + if not specified. There is no need to specify a cents value of zero (.00).
• A number—Any valid integer or floating point number is acceptable. The number is converted to the money data type automatically.

On output, money values display as strings of 20 characters with a default precision of two decimal places. The display format is:

$[-]dddddddddddd.dd

where:

$ is the default currency symbol
d is a digit from 0 to 9

The following settings affect the display of money data. For details, see the System Administrator Guide:

Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:

select col1, ifnull(col2, 0), left(col4, 22) from t1:

Logical Key Data Types

The logical key data type allows the DBMS Server or your application to assign a unique key value to each row in a table. Logical keys are useful when an application requires a table to have a unique key, and the columns of the table do not comprise a unique key.

Types of Logical Keys

There are two types of logical keys:

• System_maintained—The DBMS Server automatically assigns a unique value to the column when a row is appended to the table. Users or applications cannot change system_maintained columns. When the column is created, the DBMS Server assigns it the option, not null with default. An error is returned if any option other than not null with default is specified.
• Not system_maintained—The DBMS Server does not assign a value to the column when a row is appended. Your application must maintain the contents of the column; users and application programs can change logical key columns that are not system_maintained. The default for logical key columns is not system_maintained.

Specify the scope of uniqueness for system_maintained logical key columns using the following options:

• Table_key—Values are unique within the table.
• Object_key—Values are unique within the entire database.

If two or more system_maintained logical key columns of the same type (object_key or table_key) are created within the same table, the same value is assigned to all columns of the same type in a given row. Different values are assigned to object and table key columns in the same row, as shown in the following diagram:

Table_key values are returned to embedded SQL programs as 8-byte char strings, and object_key values as 16-byte char strings. Values can be assigned to logical keys that are not system_maintained using string literals. For example:

insert into keytable(table_key_column)
values('12345678');

Values assigned to table_keys must be 8-character strings; values assigned to object_keys must be 16-character strings.

Restrictions on Logical Keys

When working with logical keys, be aware of the following restrictions:

• A system_maintained logical key column cannot be created using the create table..as select statement. A not system_maintained data type is assigned to the resulting column.
• The copy statement cannot be used to load values from a file into a system_maintained column.

Binary Data Types

There are three binary data types:

• Byte
• Byte varying
• Long byte

Binary columns can contain data such as graphic images, which cannot easily be stored using character or numeric data types.

Byte Data Types

The byte data type is a fixed length binary data type. If the length of the data assigned to a byte column is less than the declared length of the column, the value is padded with zeros to the declared length when it is stored in a table. The minimum length of a byte column is 1 byte, and the maximum length is limited by the maximum row width configured but not exceeding 32,000.

Byte Varying Data Types

The byte varying data type is a variable length data type. The actual length of the binary data is stored with the binary data, and, unlike the byte data type, the data is not padded to its declared length. The minimum length of a byte varying column is 1 byte, and the maximum length is limited by the maximum row width configured but not exceeding 32,000.

Long Byte Data Types

The long byte data type has the same characteristics as the byte varying data type, but can accommodate binary data up to 2 GB in length. In embedded SQL data handlers can be created, which are routines to read and write the data for long byte columns. For details about data handlers, see Handlers for Large Objects in the chapter "Embedded SQL" and the Embedded SQL Companion Guide.

Restrictions to Long Byte Data Types

The following restrictions apply to long byte columns:

• They cannot be part of a table key.
• They do not declare a length
• They cannot be part of a secondary index.
• They cannot be used in the order by group, or by clause of a select statement.
• They cannot have query optimization statistics. For details about query optimization statistics, see the discussion of the optimizedb utility in the Command Reference Guide.
• The following string functions do not work with long byte columns:
  • Locate
  • Pad
  • Shift
  • Squeeze
  • Trim
  • Notrim
  • Charextract
• Long byte columns cannot be directly compared to other data types. To compare a long byte column to another data type, apply a coercion function.
• A literal of more than 2000 bytes cannot be assigned to a long byte column. For details about assigning long values to long byte columns, see the description of data handlers in the Embedded SQL Companion Guide, Dynamic Programming in the chapter "Embedded SQL" or the OpenAPI User Guide.