PostgreSQL Data Types

Jeff Villamin
Jul 26, 2017 · 2 min read

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.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade