PostgreSQL Data Types

With PostgreSQL, there are three main data types: string, numeric, and temporal. Here are a few of the most widely-used data types.

String data types:

  1. char(n) — Fixed-length string, where n is the number of characters to store.
  2. varchar(n) — Variable-length string, you can store up to n characters.
  3. text — Variable-length string where you do not need to specify the length.

Number data types:

  1. int — 4-byte signed integer.
  2. Serial — Same as integer except that it is an auto-incrementing integer value that PostgreSQL will generate.
  3. numeric(p, s) — Where p is the total digits and s is the number of digits after the decimal.
  4. money — Currency value.
  5. boolean — Logical boolean data type that can hold three values: true, false, or NULL.

Temporal data types:

  1. date — Stores date values only, displayed as ‘YYYY-MM-DD’.
  2. time — Stores time values only, displayed as ‘HH:MM:SS’.
  3. timestamp — Stores date and time values, displayed as ‘YYYY-MM-DD HH:MM:SS’.
  4. timestamptz — Similar to timestamp with the timezone included, displayed as ‘YYYY-MM-DD HH:MM:SS-TZ’.

There are also a few popular PostgreSQL data types which don’t really fit in the above categories like json: textual JSON data, arrays: an arrays of strings, an array of integers, etc in an array column of a table.

Lastly, users can add new types to PostgreSQL using the CREATE TYPE command which lets a user define a new data type for use in the current database.