PostgreSQL Data Types and Sizes with Examples

The data types define the types of data that can be kept in a table column. PostgreSQL provides various data types for different needs. In this post, you will learn about various data types and sizes in PostgreSQL. In addition, you can create your own type in PostgreSQL.

Data Types Categories

We can divide the PostgreSQL data types into the following categories:.

  1. Numeric
  2. Monetary
  3. Character
  4. Date Time
  5. Boolean
  6. Enumeration
  7. Geometric
  8. Network Address
  9. XML
  10. JSON
  11. Array

Numeric Data Types

Numeric data types are used for storing the numbers. Choose between numeric data types based on the minimum or maximum numeric values you need to store or based on the fraction digits in a decimal number.

For example, if you need to store only numbers without fractions, then choose smallint, integer, or bigint data types based on the range; if you need to store decimal numbers, then go for decimal, real, or double precision data types. Below is a table of numeric data types and sizes in bytes.

Numeric Data Type NameStorage SizeRange
smallint2 bytes-32768 to +32767
integer4 bytes-2147483648 to +2147483647 (Around 2 billion)
bigint8 bytes-9223372036854775808 to
+9223372036854775807 (Around 9 quintillion)
decimal, numericVariablesupports 131072 digits before decimal and 16383 digits after decimal
real (float4)4 bytessupports up to 5 decimal digits
double precision (float8)8 bytessupports up to 15 decimal digits.
smallserial2 bytes1 to 32767 (auto-increment integer)
serial4 bytes1 to 2147483647 (auto-increment integer)
bigserial8 bytes1 to 9223372036854775807 (auto-increment integer)
Numeric Data Types in PostgreSQL

Smallint, integer, or big integer data types are used for storing only the numbers without decimals.

Decimal and numeric data types are similar. When defining numeric data types, we need to specify the length of the number and the digits after the decimal. For example, in numeric (4,2), we can store values between -99.99 and 99.99.

Real and double precision data types are used to store decimal numbers without specifying the total length of the number or digits after the decimal. In real data, you can store up to 5 fractional digits, and in double precision, you can store up to 15 fractional digits. If you provide more fractional digits after the supported digits, then PostgreSQL will be rounded to the supported fractional digits.

Smallserial, serial, and big serial data types are used for creating an auto-increment primary key in a table.

Below is an example of numeric datatypes in PostgreSQL.

CREATE TABLE numeric_data (
    smallint_col SMALLINT,
    integer_col INTEGER,
    bigint_col BIGINT,
    decimal_col DECIMAL(10, 2),
    numeric_col NUMERIC(10, 2),
    real_col REAL,
    double_precision_col DOUBLE PRECISION

INSERT INTO numeric_data (smallint_col, integer_col, bigint_col, decimal_col, numeric_col, real_col, double_precision_col)
    (123, 456789, 123456789012345, 1234.56, 4321.78, 1234.56789, 1234.123456789912345);
Numeric datatypes examples

Monetary Data Types

The monetary data types are used to store currency amounts with two fractional digits. PostgreSQL provides only one monetary data type.

Monetary Data TypeStorage SizeRange
money8 bytes-92233720368547758.08
+92233720368547758.07 (Around 92 Quadrillion)
PostgreSQL Monetary Data Types

Below is an example of creating a table with money data.

	id serial primary key,
	name varchar(100),
	price money

By default, always show the dollar ($) symbol when showing the money data. You can change this setting by changing the LC_MONETARY locale.

PostgreSQL money data type example

Money Type Casting

You can cast any numeric data type to a money data type except real and double precision. To convert real and double precision data types to money, you need to convert them to numeric data types first, and then you can convert them to money data types as shown below.

Casting error from real to money data type
SELECT 12.598726::real::numeric::money
Casting from real data type to money data type

Character Data Types

To store the string data in tables, we need character data types. Character data types come in three types:

  1. Fixed length
  2. Variable length
  3. Unlimited length

The size of character types is based on the character’s length. The size of a string is equal to its character length plus one byte for shorter strings up to 126 bytes, while an additional 4 bytes are required for longer strings.

Character Data Type NameDescription
character varying(n)/varchar(n)For variable-length string
character(n)/char(n)For fixed-length string
textFor unlimited-length string
Character Data Types in PostgreSQL

Here, “n” is a positive integer. Character and Varchar data types can store characters up to the “n” length. When you try to store extra characters, PostgreSQL will throw an error.

Below is an example of character data types in PostgreSQL.

	id serial primary key,
	name character varying(50),
	class character(2),
	additional_info text

The above table is created with three character data types.

  1. name: This column can contain characters up to 50 characters.
  2. class: This column can contain characters only up to 2 lengths. If you provided only one character, then space is added to the end of the character to make it a length of 2 characters.
  3. additional_info: This column is a data-type text column. We can store any number of characters in this column.

Below is an example of an INSERT command for character data types. You have to use a single quote (‘) to enclose the characters.

INSERT INTO student (name, class, additional_info) VALUES
('Johnhy', 'II', 'character type example');

Character Data Type Casting

We can cast any other type to a character data type. Below is an example:

Character type casting from integer, real, and string data type

Extra characters from the given length of the data type will be truncated by the PostgreSQL without giving any error.

Date Time Data Types

PostgreSQL provides six data types to handle all date and time requirements.

Both date and time can be stored using the timestamp data type. You can store date and time with time zone information using the timestamptz data type.

When you need to store only date information without time, then you can use the date data type.

For storing only time-related data, use the time data type. Use the timetz data type when you need to store time information in a time zone.

The interval data type represents the interval between two datetimes. For example, if you have to subtract two dates in PostgreSQL, then the output is in the interval format, like ‘2 days’.

Below is a list of all date-time data types and sizes supported by PostgreSQL.

Date Time Data Type NameStorage SizeSupports Time zoneRangeResolution
timestamp8 bytesNo4713 BC to 294276 AD1 microsecond
timestamptz8 bytesYes4713 BC to 294276 AD1 microsecond
date4 bytesNo4713 BC to 5874897 AD1 day
time8 bytesNo00:00:00 to 24:00:001 microsecond
timetz12 bytesYes00:00:00+1559 to 24:00:00-15591 microsecond
interval16 bytesNo-178000000 years to 178000000 years1 microsecond
Data Time Data Types in PostgreSQL

An example of creating a table containing date-time data types is shown below.

CREATE TABLE example_table (
    timestamp_col TIMESTAMP,
    timestamptz_col TIMESTAMPTZ,
    date_col DATE,
    time_col TIME,
    timetz_col TIMETZ,
    interval_col INTERVAL

Below is an example of inserting date-time data according to the data types in the above table.

INSERT INTO example_table (timestamp_col, timestamptz_col, date_col, time_col, timetz_col, interval_col)
('2024-02-19 08:30:00', 
 '2024-02-19 08:30:00+02', 
 '1 day 2 hours');
PostgreSQL Data Time data types example

Boolean Data Type

A boolean represents two possible states: true and false. A boolean data type in PostgreSQL is used to store these two states. In addition, boolean also supports NULL values to handle missing data scenarios.

NameStorage Size
boolean1 byte
Boolean Data Type in PostgreSQL

Below is an example of creating a table using the boolean data type.

CREATE TABLE boolean_table
	id serial primary key,
	is_active boolean

Let’s insert data into the binary table using the below INSERT command.

INSERT INTO boolean_table (is_active) VALUES (TRUE);
INSERT INTO boolean_table (is_active) VALUES (FALSE);
Boolean Data Type example

Below is an example of boolean type usage in Filter query.

SELECT * FROM boolean_table WHERE is_active=TRUE
Boolean data type filter query example

Enumerated Type

In the enumerated type, we can declare some static values that are only allowed in that enum column. For example, if we create an enum type in which “ABC” or “CBA” strings are allowed and the user tries to enter any other value, then PostgreSQL will throw an error.

Enum static values are case-sensitive and each enum value takes 4 bytes on disk.

In the below example, we are creating a new enum “weekdays,” in which we are giving static values for weekdays.


CREATE TABLE enum_example_table
	id serial primary key,
	ActionDay weekdays

Below is an example of an insert query for an enum type.

NSERT INTO enum_example_table (ActionDay) VALUES ('MON');
INSERT INTO enum_example_table (ActionDay) VALUES ('SAT');
PostgreSQL Enum INSERT query Examples

If the user tries to enter an ‘ABC’ value that is not in the weekdays enum, PostgreSQL will throw an error, as shown below.

Enum type invalid value error

Geometric Types

Geometric data types are used to store geometric shape data. Below is the full list with the representation.

NameStorage SizeRepresentation
point16 bytes(x,y)
line32 bytes{x,y,z}
lseg32 bytes((x1,y1), (x2,y2))
box32 bytes((x1,y1), (x2,y2))
path16+16n bytes((x1,y1),…) or [(x1,y1),…]
polygon40+16n bytes((x1,y1),…)
circle24 bytes<(x,y),r>
Geometric Data Types in PostgreSQL

Below is an example of geometric data types.

CREATE TABLE geometric_data (
    point_geom POINT,
    line_geom LINE,
    lseg_geom LSEG,
    box_geom BOX,
    path_geom PATH,
    polygon_geom POLYGON,
    circle_geom CIRCLE

INSERT INTO geometric_data (point_geom, line_geom, lseg_geom, box_geom, path_geom, polygon_geom, circle_geom)
(POINT(1, 2), 
 LINE '(1,2),(3,4)', 
 LSEG '((1,2),(3,4))', 
 BOX '(1,2),(3,4)', 
 PATH '((1,2),(3,4),(5,6))', 
 POLYGON '((1,2),(3,4),(5,6),(1,2))', 
 CIRCLE '((1,2),3)');
PostgreSQL Geometric data types example

Network Address Data Types

PostgreSQL provides network data types for storing IP addresses, subnet masks, and MAC addresses. PostgreSQL network data types can store both IPv4 and IPv6.

Network address types provides several features to ensure data integrity. Some of the features are the following.

  1. Validate whether the data is in the correct IP format or not.
  2. Check if the IP address belongs to the subnet or not.
  3. Search IP addresses based on the range.
Network Data Type NameStorage SizeDescription
inet4 bytes for IPv4, 16 bytes for IPv6Use for storing IP addresses
cidr4 bytes for IPv4
16 bytes for IPv6
5 bytes for IPv4 + subnet
18 bytes for IPv6 + subnet
Use for storing IP addresses and subnet information
macaddr6 bytesUse for storing MAC addresses
macaddr88 bytesStore MAC address in EUI-64 format
Network Data Types in PostgreSQL

Below is an example of network address data types.

CREATE TABLE network_devices (
    device_name VARCHAR(50),
    ipv4_addr INET,
    ipv6_addr INET,
    ip_subnet CIDR,
    mac_addr MACADDR,
    mac_addr_eui MACADDR8

INSERT INTO network_devices (device_name, ipv4_addr, ipv6_addr, ip_subnet, mac_addr, mac_addr_eui) VALUES
('Home Router', 
Network address Data Types example

Below is an example of in-build validation on the inet data type.

PostgreSQL Validation error message on inet data type

Getting a broadcast address from a CIDR address in PostgreSQL.

SELECT broadcast(ip_subnet) FROM network_devices;
Getting broadcast address from cidr address

Query for validating IP address belongs to a subnet or not.

PostgreSQL Validating IP address belongs to particular subnet

XML Data Type

PostgreSQL provides the XML data type to store XML data. XML data types provide excellent querying features that allow you to extract specific XML elements or XML tags.

NameStorage SizeDescription
xmlsize depends on the xml content length and encodingStore XML data in tree structure
PostgreSQL XML Data Types

Example of PostgreSQL XML data type

CREATE TABLE products (
	name varchar(255) NOT NULL,
	details XML

INSERT INTO products (name, details) VALUES
	XML '<product>
XML Data type example

JSON Data Types

JavaScript Object Notation (JSON) is a standard for representing structured data. It is independent of any programming language. The most popular use of JSON is in REST APIs. You can store JSON data in the PostgreSQL database using JSON data types.

PostgreSQL only allows valid JSON data in the JSON data type column.

NameStorage SizeDescription
jsonSize of input text + additional overheadStores JSON data as text
jsonbSize of input text + additional overheadstores JSON data in binary format
PostgreSQL JSON Data Types

The json data type stores data as text, and jsonb stores data in binary format. JSONB also supports indexing for faster performance.

An example of JSON data types.

CREATE TABLE json_data
	id serial primary key,
	json_data json,
	jsonb_data jsonb

INSERT INTO json_data (json_data, jsonb_data) VALUES
('{"name" : "json name", "dob" : { "year": 1980, "month": 12, "day": 12}}', 
 '{"name" : "jsonb name", "dob" : { "year": 1980, "month": 12, "day": 12}}')
JSON Data types examples

We can also use ->> operator to filter JSON data. Below is an example.

SELECT * FROM json_data
WHERE json_data->>'name'='json name';
Filter JSON data example

Array Data Type

PostgreSQL allows you to create an array type that can store collections of values of the same type in a single field. To declare the array type, put square brackets [] after the data type.

We can create both one-dimensional and two-dimensional array types.

	id serial primary key,
	items varchar(50)[]

INSERT INTO shopping (items) VALUES
('{"Orange", "Apple", "Banana"}');
Array data type example in PostgreSQL

To access an individual array item, use the index number after column name in square brackets. Index numbers starts from the 1. For example, in above screenshot, “Orange” item is on Index 1, and Apple is on index 2.

Below is an example of filter query based on the Array data type.

SELECT * FROM shopping where items[2] = 'Apple';

Accessing Array items in PostgreSQL query


PostgreSQL provides a range of data types, including numeric, character, date-time, boolean, geometric, XML, JSON, and array. Choose the right data type for efficient storage and optimal performance.

Read More

  1. Alter Column Type in PostgreSQL
  2. Insert Multiple Rows at once in PostgreSQL