Other Data Types

I l @ ve RuBoard

The following are various data types that are used less frequently. Often, these are used for internal system purposes only, but you might run across them, so they are listed here for completeness.

BIT and BIT VARYING

The BIT type stores a series of binary-1 and binary-0 values. The BIT data type has a specified width and pads empty entries with zeros, whereas the BIT VARYING data type allows flexible-width entries to be made.

MONEY

The MONEY data type is still supported but is no longer considered active. Consider using a NUMERIC or DECIMAL data type with an appropriately set decimal width.

NAME

The NAME data type stores a 31-character string, but it is only intended to be used internally. PostgreSQL makes use of the NAME type to store information in the internal system catalogs.

OID

The OID data type is an integer that ranges in value from zero to 4 billion. Every object created in PostgreSQL has an OID assigned to it implicitly. OID s are useful for maintaining data integrity because that number will be unique in the database.

By default, OID s are hidden from view, but they can be selected and displayed by explicitly specifying them in a query. For instance:

 SELECT * FROM test;  Name        Age  ------------- Bill        34  Ann         22  SELECT oid, * FROM test;  oid        Name     Age  ----------------------- 19278      Bill     34  19279      Ann      22 

Often, OID s are used instead of creating explicit sequences to ensure data integrity. However, it is generally advisable not to do this for a number of reasons. OIDs exist for all objects in an entire database; therefore, they will not be sequential in any given table.

Moreover, once an OID sequence has reached its upper limit, it starts again at zero (or another prescribed minimum). Although sequences can do the same thing, the odds of OID s wrapping around are much greater because they are distributed throughout the entire database.

Additionally, when building database-driven applications, there are numerous cases in which the next value of a sequence is needed before it has actually been committed into use. Due to the reasons previously stated, it is far more reliable to query the next value of a single sequence than to try to guess the next value of an OID .

I l @ ve RuBoard