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:
- char(n) — Fixed-length string, where n is the number of characters to store.
- varchar(n) — Variable-length string, you can store up to n characters.
- text — Variable-length string where you do not need to specify the length.
Number data types:
- int — 4-byte signed integer.
- Serial — Same as integer except that it is an auto-incrementing integer value that PostgreSQL will generate.
- numeric(p, s) — Where p is the total digits and s is the number of digits after the decimal.
- money — Currency value.
- boolean — Logical boolean data type that can hold three values: true, false, or NULL.
Temporal data types:
- date — Stores date values only, displayed as ‘YYYY-MM-DD’.
- time — Stores time values only, displayed as ‘HH:MM:SS’.
- timestamp — Stores date and time values, displayed as ‘YYYY-MM-DD HH:MM:SS’.
- 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.