Sunday, January 1, 2012

Postgresql - psql : list of available datatypes

This post is just to honor the wide array of data types available in PostgreSQL. This list is available using psql's internal command \dT [pattern]

For instance:
postgres=# \dT+ *
                                                               Listado de tipos de dato
  Esquema   |           Nombre            |  Nombre interno  | TamaƱo | Elementos |                            DescripciĆ³n                            
------------+-----------------------------+------------------+--------+-----------+-------------------------------------------------------------------
 pg_catalog | "any"                       | any              | 4      |           | 
 pg_catalog | "char"                      | char             | 1      |           | single character
 pg_catalog | abstime                     | abstime          | 4      |           | absolute, limited-range date and time (Unix system time)
 pg_catalog | aclitem                     | aclitem          | 12     |           | access control list
 pg_catalog | anyarray                    | anyarray         | var    |           | 
 pg_catalog | anyelement                  | anyelement       | 4      |           | 
 pg_catalog | anyenum                     | anyenum          | 4      |           | 
 pg_catalog | anynonarray                 | anynonarray      | 4      |           | 
 pg_catalog | bigint                      | int8             | 8      |           | ~18 digit integer, 8-byte storage
 pg_catalog | bit                         | bit              | var    |           | fixed-length bit string
 pg_catalog | bit varying                 | varbit           | var    |           | variable-length bit string
 pg_catalog | boolean                     | bool             | 1      |           | boolean, 'true'/'false'
 pg_catalog | box                         | box              | 32     |           | geometric box '(lower left,upper right)'
 pg_catalog | bytea                       | bytea            | var    |           | variable-length string, binary values escaped
 pg_catalog | character                   | bpchar           | var    |           | char(length), blank-padded string, fixed storage length
 pg_catalog | character varying           | varchar          | var    |           | varchar(length), non-blank-padded string, variable storage length
 pg_catalog | cid                         | cid              | 4      |           | command identifier type, sequence in transaction id
 pg_catalog | cidr                        | cidr             | var    |           | network IP address/netmask, network address
 pg_catalog | circle                      | circle           | 24     |           | geometric circle '(center,radius)'
 pg_catalog | cstring                     | cstring          | var    |           | 
 pg_catalog | date                        | date             | 4      |           | date
 pg_catalog | double precision            | float8           | 8      |           | double-precision floating point number, 8-byte storage
 pg_catalog | fdw_handler                 | fdw_handler      | 4      |           | 
 pg_catalog | gtsvector                   | gtsvector        | var    |           | GiST index internal text representation for text search
 pg_catalog | inet                        | inet             | var    |           | IP address/netmask, host address, netmask optional
 pg_catalog | int2vector                  | int2vector       | var    |           | array of int2, used in system tables
 pg_catalog | integer                     | int4             | 4      |           | -2 billion to 2 billion integer, 4-byte storage
 pg_catalog | internal                    | internal         | 4      |           | 
 pg_catalog | interval                    | interval         | 16     |           | @  , time interval
 pg_catalog | language_handler            | language_handler | 4      |           | 
 pg_catalog | line                        | line             | 32     |           | geometric line (not implemented)
 pg_catalog | lseg                        | lseg             | 32     |           | geometric line segment '(pt1,pt2)'
 pg_catalog | macaddr                     | macaddr          | 6      |           | XX:XX:XX:XX:XX:XX, MAC address
 pg_catalog | money                       | money            | 8      |           | monetary amounts, $d,ddd.cc
 pg_catalog | name                        | name             | 64     |           | 63-character type for storing system identifiers
 pg_catalog | numeric                     | numeric          | var    |           | numeric(precision, decimal), arbitrary precision number
 pg_catalog | oid                         | oid              | 4      |           | object identifier(oid), maximum 4 billion
 pg_catalog | oidvector                   | oidvector        | var    |           | array of oids, used in system tables
 pg_catalog | opaque                      | opaque           | 4      |           | 
 pg_catalog | path                        | path             | var    |           | geometric path '(pt1,...)'
 pg_catalog | pg_node_tree                | pg_node_tree     | var    |           | string representing an internal node tree
 pg_catalog | point                       | point            | 16     |           | geometric point '(x, y)'
 pg_catalog | polygon                     | polygon          | var    |           | geometric polygon '(pt1,...)'
 pg_catalog | real                        | float4           | 4      |           | single-precision floating point number, 4-byte storage
 pg_catalog | record                      | record           | var    |           | 
 pg_catalog | refcursor                   | refcursor        | var    |           | reference to cursor (portal name)
 pg_catalog | regclass                    | regclass         | 4      |           | registered class
 pg_catalog | regconfig                   | regconfig        | 4      |           | registered text search configuration
 pg_catalog | regdictionary               | regdictionary    | 4      |           | registered text search dictionary
 pg_catalog | regoper                     | regoper          | 4      |           | registered operator
 pg_catalog | regoperator                 | regoperator      | 4      |           | registered operator (with args)
 pg_catalog | regproc                     | regproc          | 4      |           | registered procedure
 pg_catalog | regprocedure                | regprocedure     | 4      |           | registered procedure (with args)
 pg_catalog | regtype                     | regtype          | 4      |           | registered type
 pg_catalog | reltime                     | reltime          | 4      |           | relative, limited-range time interval (Unix delta time)
 pg_catalog | smallint                    | int2             | 2      |           | -32 thousand to 32 thousand, 2-byte storage
 pg_catalog | smgr                        | smgr             | 2      |           | storage manager
 pg_catalog | text                        | text             | var    |           | variable-length string, no limit specified
 pg_catalog | tid                         | tid              | 6      |           | (block, offset), physical location of tuple
 pg_catalog | time with time zone         | timetz           | 12     |           | time of day with time zone
 pg_catalog | time without time zone      | time             | 8      |           | time of day
 pg_catalog | timestamp with time zone    | timestamptz      | 8      |           | date and time with time zone
 pg_catalog | timestamp without time zone | timestamp        | 8      |           | date and time
 pg_catalog | tinterval                   | tinterval        | 12     |           | (abstime,abstime), time interval
 pg_catalog | trigger                     | trigger          | 4      |           | 
 pg_catalog | tsquery                     | tsquery          | var    |           | query representation for text search
 pg_catalog | tsvector                    | tsvector         | var    |           | text representation for text search
 pg_catalog | txid_snapshot               | txid_snapshot    | var    |           | txid snapshot
 pg_catalog | unknown                     | unknown          | var    |           | 
 pg_catalog | uuid                        | uuid             | 16     |           | UUID datatype
 pg_catalog | void                        | void             | 4      |           | 
 pg_catalog | xid                         | xid              | 4      |           | transaction id
 pg_catalog | xml                         | xml              | var    |           | XML content
(73 filas)

And if they're not enough, you can always define your very own data types.

No comments:

Post a Comment