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 Name	Storage Size	Range
smallint	2 bytes	-32768 to +32767
integer	4 bytes	-2147483648 to +2147483647 (Around 2 billion)
bigint	8 bytes	-9223372036854775808 to +9223372036854775807 (Around 9 quintillion)
decimal, numeric	Variable	supports 131072 digits before decimal and 16383 digits after decimal
real (float4)	4 bytes	supports up to 5 decimal digits
double precision (float8)	8 bytes	supports up to 15 decimal digits.
smallserial	2 bytes	1 to 32767 (auto-increment integer)
serial	4 bytes	1 to 2147483647 (auto-increment integer)
bigserial	8 bytes	1 to 9223372036854775807 (auto-increment integer)

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 (
    id SERIAL PRIMARY KEY,
    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)
VALUES 
    (123, 456789, 123456789012345, 1234.56, 4321.78, 1234.56789, 1234.123456789912345);

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 Type	Storage Size	Range
money	8 bytes	-92233720368547758.08 to +92233720368547758.07 (Around 92 Quadrillion)

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

CREATE TABLE checkout
(
	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.

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.

SELECT 12.598726::real::numeric::money

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 Name	Description
character varying(n)/varchar(n)	For variable-length string
character(n)/char(n)	For fixed-length string
text	For unlimited-length string

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.

CREATE TABLE student
(
	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:

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 Name	Storage Size	Supports Time zone	Range	Resolution
timestamp	8 bytes	No	4713 BC to 294276 AD	1 microsecond
timestamptz	8 bytes	Yes	4713 BC to 294276 AD	1 microsecond
date	4 bytes	No	4713 BC to 5874897 AD	1 day
time	8 bytes	No	00:00:00 to 24:00:00	1 microsecond
timetz	12 bytes	Yes	00:00:00+1559 to 24:00:00-1559	1 microsecond
interval	16 bytes	No	-178000000 years to 178000000 years	1 microsecond

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

CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    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)
VALUES 
('2024-02-19 08:30:00', 
 '2024-02-19 08:30:00+02', 
 '2024-02-19', 
 '08:30:00', 
 '08:30:00+02', 
 '1 day 2 hours');

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.

Name	Storage Size
boolean	1 byte

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);

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

SELECT * FROM boolean_table WHERE is_active=TRUE

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 TYPE weekdays AS ENUM ('MON','TUE','WED','THU','FRI','SAT')

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');

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

Geometric Types

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

Name	Storage Size	Representation
point	16 bytes	(x,y)
line	32 bytes	{x,y,z}
lseg	32 bytes	((x1,y1), (x2,y2))
box	32 bytes	((x1,y1), (x2,y2))
path	16+16n bytes	((x1,y1),…) or [(x1,y1),…]
polygon	40+16n bytes	((x1,y1),…)
circle	24 bytes	<(x,y),r>

Below is an example of geometric data types.

CREATE TABLE geometric_data (
    id SERIAL PRIMARY KEY,
    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)
VALUES 
(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)');

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 Name	Storage Size	Description
inet	4 bytes for IPv4, 16 bytes for IPv6	Use for storing IP addresses
cidr	4 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
macaddr	6 bytes	Use for storing MAC addresses
macaddr8	8 bytes	Store MAC address in EUI-64 format

Below is an example of network address data types.

CREATE TABLE network_devices (
    id SERIAL PRIMARY KEY,
    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', 
 '192.168.1.0', 
 '::ffff:10.0.0.1', 
 '192.168.1.0/24', 
 '00:00:5e:00:53:af',
 '00:21:2F:FF:FE:B5:6E:10'
)

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

Getting a broadcast address from a CIDR address in PostgreSQL.

SELECT broadcast(ip_subnet) FROM network_devices;

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

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.

Name	Storage Size	Description
xml	size depends on the xml content length and encoding	Store XML data in tree structure

Example of PostgreSQL XML data type

CREATE TABLE products (
	id SERIAL PRIMARY KEY,
	name varchar(255) NOT NULL,
	details XML
)

INSERT INTO products (name, details) VALUES
(
	'TShirt',
	XML '<product>
			<color>red</color>
			<size>L</size>
			<material>cotton</material>
		</product>'
)

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.

Name	Storage Size	Description
json	Size of input text + additional overhead	Stores JSON data as text
jsonb	Size of input text + additional overhead	stores JSON data in binary format

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}}')

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

SELECT * FROM json_data
WHERE json_data->>'name'='json name';

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.

CREATE TABLE shopping
(
	id serial primary key,
	items varchar(50)[]
);

INSERT INTO shopping (items) VALUES
('{"Orange", "Apple", "Banana"}');

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';

Summary

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