About InterBase Data Types

From InterBase
Jump to: navigation, search

Go Up to Specifying Data Types


When creating a new column in an InterBase table, the primary attribute that you must define is the data type, which establishes the set of valid data that the column can contain. Only values that can be represented by that data type are allowed. Besides establishing the set of valid data that a column can contain, the data type defines the kinds of operations that you can perform on the data. For example, numbers in INTEGER columns can be manipulated with arithmetic operations, while CHARACTER columns cannot.

The data type also defines how much space each data item occupies on the disk. Choosing an optimum size for the data value is an important consideration when disk space is limited, especially if a table is very large. InterBase supports the following data types:

  • INTEGER and SMALLINT
  • FLOAT and DOUBLE PRECISION
  • NUMERIC and DECIMAL
  • DATE, TIME,andTIMESTAMP
  • CHARACTER and VARYING CHARACTER
  • BOOLEAN
  • BLOB
InterBase provides the Blob data type to store data that cannot easily be stored in one of the standard SQL data types. A BLOB is used to store data objects of indeterminate and variable size, such as bit-mapped graphics images, vector drawings, sound files, video segments, chapter or book-length documents, or any other kind of multimedia information.
InterBase also supports arrays of most data types. An array is a matrix of individual items composed of any single InterBase data type (except BLOB). An array can have from 1 to 16 dimensions. An array can be handled as a single entity, or manipulated item-by-item.

A TIMESTAMP data type is supported that includes information about year, month, day of the month, and time. The TIMESTAMP data type is stored as two long integers, and requires conversion to and from InterBase when entered or manipulated in a host-language program. The DATE data type includes information on the year, month, and day of the month. The TIME data type includes information about time in hours, minutes, seconds, and tenths, hundredths, and thousandths of seconds.

The following table describes the data types supported by InterBase:

Name Size Range/Precision Description

BLOB

Variable

  • None
  • Blob segment size is limited to 64K
  • Dynamically sizable data type for storing large data such as graphics, text, and digitized voice
  • Basic structural unit is the segment
  • Blob subtype describes Blob contents

BOOLEAN

16 bits

  • TRUE
  • FALSE
  • UNKNOWN
  • Represents truth values TRUE, FALSE, and UNKNOWN
  • Requires ODS 11 or higher, any dialect

CHAR(<n>)

<n> characters

  • 1 to 32,767 bytes
  • Character set character size determines the maximum number of characters that can fit in 32K
  • Fixed length CHAR or text string type
  • Alternate keyword: CHARACTER

DATE

32 bits

1 Jan 100 a.d.
to 29 Feb 32768 a.d.

  • Stores a date as a 32-bit longword

DECIMAL ­(<precision>, <­scale>)

Variable
(16, 32, or
64 bits)

  • <precision> = 1 to 18; specifies at least <precision> digits of precision to store
  • <scale> = 0 to 18; specifies number of decimal places must be less than or equal to <precision>
  • Number with a decimal point <scale> digits from the right
  • Example: DECIMAL(10,3) holds numbers accurately in the following ­format: ppppppp.sss

DOUBLE ­PRECISION

64 bits1

2.225 x 10–308 to 1.797 x 10308

IEEE double precision: 15 digits

FLOAT

32 bits

1.175 x 10–38 to 3.402 x 1038

IEEE single precision: 7 digits

INTEGER

32 bits

–2,147,483,648 to 2,147,483,647

Signed long (longword)

NUMERIC ­(<precision>, <­scale>)

Variable (16, 32, or
64 bits)

  • <precision> = 1 to 18; specifies exactly <precision> digits of precision to store
  • <scale> = 0 to 18; specifies number of decimal places and must be less than or equal to <precision>
  • Number with a decimal point <scale> digits from the right
  • Example: NUMERIC(10,3) holds numbers accurately in the following ­format: ppppppp.sss

SMALLINT

16 bits

–32,768 to 32,767

Signed short (word)

TIME

32 bits

0:00 AM-23:59:59.9999 PM

Unsigned integer of InterBase type ISC_TIME: time of day, in units of 0.0001 seconds since midnight

TIMESTAMP

64 bits

1 Jan 100 a.d.
to 29 Feb 32768 a.d.

InterBase type ISC_TIMESTAMP; combines DATE and TIME information

VARCHAR (<n>)

<n> characters

  • 1 to 32,765 bytes
  • Character set character size determines the maximum number of characters that can fit in 32K
  • Variable length CHAR or text string type
  • Alternate keywords: CHAR VARYING, CHARACTER VARYING
  1. Actual size of DOUBLE is platform-dependent. Most platforms support the 64-bit size.

Advance To: