Data Types

Null values

• A null value indicates the lack of a value (undefined) (unknown value)
• A null value is different than a numeric 0 or a char blank space

Uses

• Value not yet known
• Value does not exist yet
• Field is not appropriate for the current record
• Value is out of range

Exact numerics

• Stored as a single value: nnn, nnnn.nnn (i.e. 123, 123.45)

integer

• 123
  
• Default integer size; equal to, or possibly greater than, CPU integer size

smallint

• 123
  
• CPU integer size

numeric

• 123.45
  
• Explicit precision as specified

• numeric :  Default precision and scale
• numeric(p) :  Explicit precision and default scale
• numeric(p,s) :  Explicit precision and scale

decimal

• 123.45
• Like numeric, but will always use at least the default level of precision

Approximate numerics

• Stored as a mantissa-exponent pair:  (nnnn, nnn) (1.23E-45)

real

• 1.23E-45
• Single-precision floating point number
• Default level of precision for the mantissa and exponent

double precision

• 1.23E-45
• Double-precision floating point number
• Default level of precision for the mantissa and exponent
• The precision of the mantissa and the exponent is equal to, or as much as twice as large as, the precision of real

float

• 1.23E-45
• Explicit precision
• More portable than real or double precision
• float(n)
  

Character strings

• 'text'
• char / character

• Fixed-length string
• Specified number of characters (default: 1)
• Always stores the specified number of characters
• Automatically appends blank spaces to the strings to the specified length (when the data is stored)
• Trailing blank spaces are removed when the data is retrieved

• char
• char(n)
• char(n)  character set <name>

varchar / character varying

• Variable-length string
• Specified number of characters (no default)
• Stores exactly the number of characters the user enters, plus one byte for the length
• Maximum of 255 characters
• The strings are not padded with blank spaces
• Trailing blank spaces are removed when the data is stored

• varchar(n)
• varchar(n)  character set <name>

national character / national character varying

• Same as above, but using a different character set

text

• Varchar that can be of any length (up to a set maximum size)
• Default values are not allowed
• Trailing spaces are not added or removed when the data is stored or retrieved
• Not a standard data type
• Can make indexes for text columns (have to specify an index length for the column)
  

Bit strings

• Binary, hexadecimal, and boolean data
• B'11011'
• X'77FF'

bit

• B'11011'
• X'77FF'
• Fixed-length bit string
• Specified number of bits (default: 1)

• bit
• bit(n)

bit varying

• B'11011'
• X'77FF'
• Variable-length bit string
• Specified number of bits (no default)

• bit varying(n)

blob

• Binary large object
• Binary varchar that can be of any length (up to a set maximum size)
• Case sensitive
• Default values are not allowed
• Trailing spaces are not added or removed when the data is stored or retrieved
• Not a standard data type

• Can make indexes for blob columns (have to specify an index length for the column)

Datetimes

date

• '2002-01-31'
• Stores year, month and day
• The year is four digits
• The month and day are two digits
• The length is 10 positions (yyyy-mm-dd)

time

• '12:34:56'
• '12:34:56.78'
• Stores hour, minute, and second
• Hour and minute are two digits
• Second is two digits, with an optional fractional part
• Specified fractional precision (default: 0)
• The length is 8 positions without fractional seconds (hh:mm:ss)
• The length is 9 positions plus fractional-precision otherwise (hh:mm:ss.sss)
• The actual precision of the fractional part is implementation dependent, but at least 6 digits

• time
• time(n)   (fractional precision of n)

timestamp

• '2002-01-31 12:34:56.78'
• Includes both date and time
• Specified fractional precision (default: 6)
• The length is 19 positions without fractional seconds (yyyy-mm-dd hh:mm:ss)
• The length is 20 positions plus fractional-precision otherwise (yyyy-mm-dd hh:mm:ss.sss)

• timestamp
• timestamp(n)   (fractional precision of n)

time with time zone

• '12:34:56.78-08:00'
• Same as time, but also stores the offset from universal time (GMT ( Greenwich Mean Time))
• The offset ranges from -12:59 to +13:00
• The length is 14 positions without fractional seconds (hh:mm:ss-hh:mm)
• The length is 15 positions plus fractional-precision otherwise (hh:mm:ss.sss-hh:mm)

timestamp with time zone

• '2002-01-31 12:34:56.78-08:00'
• Same as timestamp, but also stores the offset from universal time (GMT ( Greenwich Mean Time))
• The length is 25 positions without fractional seconds (yyyy-mm-dd hh:mm:ss-hh:mm)
• The length is 26 positions plus fractional-precision otherwise (yyyy-mm-dd hh:mm:ss.sss-hh:mm)

Intervals

• Distance between two datetime values
• interval '12' day

year-month

• Number of years and months between two dates

• interval year
• interval month
• interval year to month

day-time

• Number of days, hours, minutes, and seconds between two instants within a month
• Operations between the two cannot be mixed due to the varying number of days in a month

• interval day
• interval day to hour
• interval day to minute
• interval day to second
• interval hour
• interval hour to minute
• interval hour to second
• interval minute
• interval minute to second
• interval second