7.1 Data Types

Every relational database is built of tables, which consist of various columns. Each column is designed to contain a special, predefined piece of data. Depending on the kind of data you want to store, an appropriate data type must be chosen. A column based on a certain data type can only store information fitting the demands of the data type of the column. For example, a column that has been defined as integer value must not be used to store text.

PostgreSQL provides a powerful set of data types, which can be retrieved by using the \dT command in psql:

 phpbook=# \dT                                        List of data types             Name             |                            Description -----------------------------+------------------------------------------------- ------------------  abstime                     | absolute, limited-range date and time (Unix system time)  aclitem                     | access control list  bigint                      | ~18 digit integer, 8-byte storage  bit                         | fixed-length bit string  bit varying                 | variable-length bit string  boolean                     | boolean, 'true'/'false'  box                         | geometric box '(lower left,upper right)'  bytea                       | variable-length string, binary values escaped  "char"                      | single character  character                   | char(length), blank-padded string, fixed storage  length  character varying           | varchar(length), non-blank-padded string, variable storage length  cid                         | command identifier type, sequence in transaction  id  cidr                        | network IP address/netmask, network address  circle                      | geometric circle '(center,radius)'  date                        | ANSI SQL date  double precision            | double-precision floating point number, 8-byte storage  inet                        | IP address/netmask, host address, netmask optional  int2vector                  | array of 16 int2 integers, used in system tables  integer                     | -2 billion to 2 billion integer, 4-byte storage  interval                    | @ <number> <units>, time interval  line                        | geometric line '(pt1,pt2)'  lseg                        | geometric line segment '(pt1,pt2)'  macaddr                     | XX:XX:XX:XX:XX:XX, MAC address  money                       | $d,ddd.cc, money  name                        | 31-character type for storing system identifiers  numeric                     | numeric(precision, decimal), arbitrary precision  number  oid                         | object identifier(oid), maximum 4 billion  oidvector                   | array of 16 oids, used in system tables  path                        | geometric path '(pt1,...)'  point                       | geometric point '(x, y)'  polygon                     | geometric polygon '(pt1,...)'  real                        | single-precision floating point number, 4-byte storage  refcursor                   | reference cursor (portal name)  regproc                     | registered procedure  reltime                     | relative, limited-range time interval (Unix delta time)  "SET"                       | set of tuples  smallint                    | -32 thousand to 32 thousand, 2-byte storage  smgr                        | storage manager  text                        | variable-length string, no limit specified  tid                         | (Block, offset), physical location of tuple  timestamp without time zone | date and time  timestamp with time zone    | date and time with time zone  time without time zone      | hh:mm:ss, ANSI SQL time  time with time zone         | hh:mm:ss, ANSI SQL time  tinterval                   | (abstime,abstime), time interval  unknown                     |  xid                         | transaction id (47 rows) 

By default PostgreSQL provides the incredible number of 47 data types. Some of these types are used only for internal purposes, but the majority of the data types are ready for use in real-world applications.