PostgreSQL Data Types

Although users are free to create custom data types using the Create Type SQL command, PostgreSQL also supports a wide variety of data types. Below are short descriptions of commonly used data types including numeric, character, boolean, and date/time, followed by a list of other available types.

Numeric

The numeric data types can be described in further detail as integer types, arbitrary precision numbers, floating point types, and serial types. Some examples of integer types would be the smallint, integer, and bigint types which store whole numbers of various ranges. The arbitrary precision numbers, such as the SQL standard types numeric or decimal, can be used to store numbers with a large amount of digits and perform calculations exactly. Floating point types, such as real and double precision, are inexact. In other words, some values are stored as approximations, and can have special values such as Infinity, -Infinity, and NaN. Lastly, the serial types, such as smallserial, serial, and bigserial, are used for notation convenience when creating unique identifier columns and is similar to the auto_increment property commonly supported by other databases.

Character

The three character types are character varying(n) (or varchar(n)), character(n) (or char(n)), and text. The (n) is a positive integer that represents the limit of the character length, while text can be of unlimited length.

Boolean

The boolean data type represents states of either true, false, or unknown (with unknown represented by the null value).

Date/Time

The date/time date type consists of the types timestamp, date, time, and interval. Both the timestamp and time types can be further specified as without time zone or with time zone.

The following is a list of other PostgreSQL supported data types (further information found at http://www.postgresql.org/docs/9.4/static/datatype.html):

  • Monetary
  • Binary
  • Enumerated
  • Geometric
  • Network Address
  • Bit String
  • Text Search
  • UUID
  • XML
  • JSON
  • Arrays
  • Composite
  • Range
  • Object Identifier
  • Pseudo
One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.