Skip to content

Data Types Supported by Postgresql

  • Boolean - 1, yes, y, t, true values are converted to true - 0, no, false, f values are converted to false.

  • Character (char, varchar, text)

CHAR(n) is the fixed-length character with space padded.
If you insert a string that is shorter than the length of the column, PostgreSQL pads spaces.
If you insert a string that is longer than the length of the column, PostgreSQL will issue an error.

VARCHAR(n) is the variable-length character string.
With VARCHAR(n),  you can store up to n characters.
PostgreSQL does not pad spaces when the stored string is shorter than the length of the column.

TEXT is the variable-length character string.
Theoretically, text data is a character string with unlimited length.
  • Numberic (integer, float)
Small integer ( SMALLINT) is 2-byte signed integer that has a range from -32,768 to 32,767.
Integer ( INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
Serial is the same as integer except that PostgreSQL will automatically generate and populate values into the SERIAL column.
This is similar to AUTO_INCREMENT column in MySQL or AUTOINCREMENT column in SQLite.

float(n)  is a floating-point number whose precision, at least, n, up to a maximum of 8 bytes.
realor float8is a 4-byte floating-point number.
numeric or numeric(p,s) is a real number with p digits with s number after the decimal point. The numeric(p,s) is the exact number.
  • Temporal Types (date, datetime, timestamp, interval)
DATE stores the dates only.
TIME stores the time of day values.
TIMESTAMP stores both date and time values.
TIMESTAMPTZ is a timezone-aware timestamp data type. It is the abbreviation for timestamp with the time zone.
INTERVAL stores periods of time.
  • UUID (for storing Universally Unique Indentifiers)
The UUID data type allows you to store Universal Unique Identifiers defined by RFC 4122 .
The UUID values guarantee a better uniqueness than SERIAL.
It can be used to hide sensitive data exposed to the public such as values of id in URL.
  • Array (store array of string, numbers etc)
In PostgreSQL, you can store an array of strings, an array of integers, etc., in array columns.
The array comes in handy in some situations e.g., storing days of the week, months of the year.
  • JSON (stores JSON data)
PostgreSQL provides two JSON data types: JSON and JSONB for storing JSON data.
The JSON data type stores plain JSON data that requires reparsing for each processing.
While JSONB data type stores JSON data in a binary format which is faster to process but slower to insert.
In addition, JSONB supports indexing, which can be an advantage.
  • hstore (stores key-value pair)
The hstore module implements the hstore data type for storing key-value pairs in a single value.
The hstore data type is very useful in many cases, such as semi-structured data or rows with many attributes that are rarely queried.
Notice that keys and values are just text strings only.
  • Special Types (Numeric Address, Geospatial Data)
box– a rectangular box.
line – a set of points.
point– a geometric pair of numbers.
lseg– a line segment.
polygon– a closed geometric.
inet– an IP4 address.
macaddr– a MAC address.