PostgreSQL Data Types

In this PostgreSQL tutorial, we will learn about PostgreSQL Data Types. Here we will learn about the different data types available in PostgreSQL with examples.

PostgreSQL Data Types

PostgreSQL upholds a vast range of Data Types. It contains several data types, including Boolean, Numeric, Character, Temporal, Array, JSON, UUID, and special data types.

When generating a table, we specify the data type for each column, indicating the type of data we want to store in the table fields.

This has a few advantages −

1. Consistency: Operations against columns of constant data types yield reliable outcomes and are typically the fastest.
2. Performance: Correct utilization of data types enables the most productive reposting of knowledge. The values held are often handled quickly, which enhances the performance.
3. Validation: Correct use of data types implies format validation of data and rejection of data outside the scope of the data type.
4. Compactness: As a column will store one type of value, it is kept in a compressed method.

Review of PostgreSQL data types. PostgreSQL has the following data types:

• Numeric data types include integer and floating-point numbers.
• Temporal types like date, time, timestamp, and interval
• UUID for storing globally distinctive Identifiers
• ARRAY for storing array strings, numbers, etc.
• JSON will store JSON data
• Special types like network address and geometric data.
• Boolean types like true, false, or null.
• Character types, therefore, are text, char, and varchar

Read Drop Database PSQL

Numeric data types in PostgreSQL

PostgreSQL provides several numeric types to accommodate various requirements for precision, range, and storage efficiency.

Integer Types

I typically use the following integer types based on the expected data range:

Data Type	Storage Size	Range	Best Used For
SMALLINT	2 bytes	-32,768 to 32,767	Small counts, flags
INTEGER	4 bytes	-2,147,483,648 to 2,147,483,647	Standard IDs, counts
BIGINT	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	Large IDs, timestamps

You can use:

• SMALLINT for product ratings (1-5 scale)
• INTEGER for product IDs and inventory counts
• BIGINT for transaction IDs in a high-volume system

Here is the list of obtainable data types.

Name	Description	Range	Storage size
integer	a typical choice for an integer	-2147483648 to +2147483647	4 bytes
decimal	user-specified precision, exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point	variable
numeric	user-specified precision, exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point	variable
real	variable-precision, inexact	6 decimal digits precision	6 bytes
serial	autoincrementing integer	1 to 2147483647	4 bytes
bigserial	large autoincrementing integer	1 to 9223372036854775807	8 bytes
smallserial	small autoincrementing integer	1 to 32767	2 bytes
smallint	small-range integer	-32768 to +32767	2 bytes
bigint	large-range integer	-9223372036854775808 to 9223372036854775807	8 bytes
double precision	variable-precision, inexact	15 decimal digits of precision	8 bytes

Floating-Point Types

For decimal values, PostgreSQL provides:

• REAL: 4 bytes, 6 decimal digits precision
• DOUBLE PRECISION: 8 bytes, 15 decimal digits precision

However, I’ve found these types can lead to rounding errors in financial applications.

Decimal Types

For financial data where precision is crucial, I always recommend:

• NUMERIC(p,s) or DECIMAL(p,s): Where p is the total digits and s is the number of decimal places

-- For storing currency values with exactly 2 decimal places
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(10,2)  -- Up to 999,999,999.99
);

The NUMERIC type stores numbers with a lot of digits. Generally, we use the NUMERIC type for numbers that require precision, such as financial amounts or quantities.
The following represents the syntax of the NUMERIC type:

NUMERIC(precision, scale)

The accuracy mentioned above refers to the total number of digits, and the scale refers to the number of digits in the fraction part. For example, the number 1234.567 has a precision of 7 and a scale of 3.

The NUMERIC type will hold a value of up to 131,072 digits before the decimal point and 16,383 digits after the decimal point.

The scale of the NUMERIC type is zero or positive. The related shows the syntax of the NUMERIC type with scale zero:

NUMERIC(precision)

If we exclude both precision and scale, we can store any precision and scale up to the limits mentioned above.

NUMERIC

In PostgreSQL, the NUMERIC and DECIMAL types are identical; both are likewise a part of the SQL standard.

If in the case of accuracy isn’t necessary, we ought not to use the NUMERIC type since estimations on NUMERIC values are generally slower than integers, floats, and double precision.

Implementation Example:

Firstly, we will create a new table named products for the demonstration

DROP TABLE IF EXISTS items;

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    cost NUMERIC(6,3)
);

Secondly, we will insert some products with costs whose scales exceed the scale declared in the price column:

INSERT INTO items (name, cost)
VALUES ('Speakers',400.214), 
       ('Earphones',700.21);

Because the scale of the cost column is 2, PostgreSQL rounds the value 400.150 up to 400.22 and rounds the value 400.214 down to 400.21

The following query returns all rows of the products table:

SELECT * FROM items;

If we store a value whose accuracy exceeds the declared precision, PostgreSQL raises an error, which is shown in the example:

INSERT INTO items (name, price)
VALUES('Speakers',123456.21);

PostgreSQL will generate the following error:

This approach prevented the rounding errors we experienced with floating-point types.

Double data types

In PostgreSQL, a numeric data type is DOUBLE PRECISION, also known by its alternate name float8.

Double-precision values are managed as floating-point values, which means that any rounding will occur if we attempt to store a value with “too many” decimal digits. For example, if we try to store the result of 2/3, rounding will occur when the 15th digit is reached.

Sometimes there is no actual result of this estimation, but there are circumstances where absolute precision is essential, and the NUMERIC data type is the better option.

Data Type	Range	Description
double-precision, float, float8	15 decimal digits of precision	decimal, floating-point numeric data, integer values

Implementation Example:

Create a database and table in PostgreSQL: Now, we will create a table with a DOUBLE PRECISION data type. Firstly, we have to create a database in PostgreSQL in which we will use the command shown below:

CREATE DATABASE any_db;

Now we will create a table:

CREATE TABLE TABLE_NAME(
        COLUMN1 + DATA_TYPE + CONSTRAINT [OPTIONAL],
        COLUMN2 + DATA_TYPE + CONSTRAINT [OPTIONAL]);

Now, we will see the statement used to create our table:

CREATE TABLE abc(
    id SERIAL PRIMARY KEY,
    str_col VARCHAR(100),
    int_col INT NOT NULL,
    boolean_col BOOL,
    float_col DOUBLE PRECISION
);

Use the command \d abc; To view the table information, the output of this command will look like this:

Insert some values inside the DOUBLE PRECISION column.

For inserting our data, we can use an INSERT statement in our DOUBLE PRECISION column and another column of the table:

INSERT INTO abc (str_col, int_col, boolean_col, float_col)
VALUES('Glass', 7896, TRUE, 0879654321),
    ('Images', 7768, FALSE, 09876544568),
    ('Tutor', 6754, TRUE, 09417689765);

To see the values in the float_column, Use the simple SELECT Statement shown below:

SELECT id, float_col FROM demo;

Now, here is the following output:

Character and Text Data Types in PostgreSQL

PostgreSQL has three-character data types: CHAR(n), VARCHAR(n), and TEXT

• CHAR(n) is a certain-sized character with space padding. If we insert a string that is shorter than the column’s length, PostgreSQL pads the string with spaces. If we insert a string that is longer than the column’s length, PostgreSQL will encounter an error.
• VARCHAR(n) is a variable-length character string. With VARCHAR(n), we store up to n characters. PostgreSQL doesn’t pad spaces when the stored string is shorter than the column’s length.
• TEXT is a variable-length character string. Hypothetically, text data is a character string with unlimited length.

I’ve found that:

• CHAR It is rarely the best choice due to storage inefficiency
• VARCHAR Works well for constrained fields like usernames, codes, and short descriptions
• TEXT It is ideal for unconstrained content like articles or comments

The table below outlines the purposes of character types available in PostgreSQL.

Name	Description
text	variable-length with limit
character varying(n), varchar(n)	variable- length with a limit
character(n), char(n)	fixed-length, blank padded

Performance Considerations

1. Use TEXT for large content instead of large VARCHAR(n)
2. Add appropriate indexes for frequently queried text columns
3. Consider text compression for very large text fields
4. Use LIKE queries with caution, especially with leading wildcards

Date and Time Data Types

The temporal data types permit us to keep date and time data. PostgreSQL has five foremost temporal data types:

• The DATE will store the dates only.
• The TIME will store the time-of-day values.
• TIMESTAMP stores both date and time values.
• TIMESTAMPTZ is a timezone-aware timestamp data type, which is an abbreviation for ‘timestamp with time zone’.
• The INTERVAL will store the periods.

The TIMESTAMPTZ is PostgreSQL’s expansion to the SQL standard’s temporal data types.

PostgreSQL supports a comprehensive set of SQL date and time types with storage, as listed in the table below.

Data Type	Storage Size	Description	Example
DATE	4 bytes	Date only	2023-07-15
TIME	8 bytes	Time only	14:30:00
TIMESTAMP	8 bytes	Date and time	2023-07-15 14:30:00
TIMESTAMPTZ	8 bytes	Date, time, with timezone	2023-07-15 14:30:00-07:00
INTERVAL	16 bytes	Time periods	2 days 3 hours

Read PostgreSQL group by

Name	Description	High Value	Low value	Storage size
TIMESTAMPTZ	both date and time, with time zone	294276 AD	4713 BC	8 bytes
timestamp [(p)] [without time zone ]	both date and time(no time zone)	294276 AD	4713 BC	8 bytes
interval [fields ] [(p) ]	time interval	178000000 years	– 178000000 years	12 bytes
time [ (p)] [ without time zone ]	time of day (no date)	24:00:00	00:00:00	8 bytes
time [ (p)] with time zone	times of day only, with time zone	24:00:00-1459	00:00:00+1459	12 bytes
date	date(no time of day)	5874897 AD	4713 BC	4 bytes

Implementation Example:

Here’s an example of a DATE A data type that uses the CURRENT_DATE() function:

CREATE TABLE Student(
id INT NOT NULL,
name TEXT,
join_date DATE DEFAULT CURRENT_DATE
);

This statement will return results that look like the following:

 SELECT * FROM Student;

UUID data type in PostgreSQL

The UUID (Universally Unique Identifier) type stores 128-bit identifiers that are practically guaranteed to be unique. In distributed systems I’ve designed for tech companies in Seattle, UUIDs offer several advantages:

• Reduced risk of ID collisions in distributed environments
• Generated without coordination between servers
• No sequential patterns that reveal business information

Implementation Example:

For Example: 660e8500-e25b-40d4-a496-336633990000.

-- Using UUID for user identifiers
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(50) UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

ARRAY Data Types

PostgreSQL permits a column in a table to be a variable-length and multidimensional array. We will build any user-defined base type, built-in, composite, and enumerated type arrays.

We will implement many operations on arrays as well as declare, insert, access, modify, and search in PostgreSQL.

PostgreSQL reserves an array of strings, an array of integers, etc., in array columns. An array that, in certain circumstances, appears to store days of the week and months of the year.

Implementation Example:

In the below example, we will create a table named Students with the contact column defined as a text array:

CREATE TABLE Students (
   id int PRIMARY KEY,
   name VARCHAR (100),
   contact TEXT []
);

The command will run successfully:

Now, we will insert the array values:

INSERT INTO Students
VALUES
   (
      1,
      'Richard Jener',
      ARRAY [ '(408)-643-9245',
      '(408)-667-8920' ]
   );

The insertion will run successfully:

Read PostgreSQL Loop Examples

JSON and JSONB Data Types

PostgreSQL supports two JSON data types: JSON and JSONB for storing JSON data.

The JSON data type stores plain JSON data, which requires reparsing for every processing step. In contrast, the JSONB data type stores JSON data in a binary format, making it quicker to measure but slower to insert.

Also, JSONB supports indexing, which can be a benefit.

Implementation Example:

Let’s understand with the help of an example by creating a table:

CREATE TABLE goods (
	id serial NOT NULL PRIMARY KEY,
	info json NOT NULL
);

The products in the table comprise two columns:

1. The id column is the primary key column that identifies the order.
2. The info column stores the data in the form of JSON.

Insert JSON data

Let’s insert the data into a JSON column; we will ensure that the data is in a valid JSON format. The statement below inserts a new row into the table.

INSERT INTO goods (info)
VALUES('{ "customer": "David Geller", "items": {"product": "Vodka","qty": 4}}');

It means "David Geller" Bought 4 bottles of vodka. The statement below inserts multiple rows simultaneously.

INSERT INTO goods (info)
VALUES('{ "customer": "Christina Applegate", "items": {"product": "Diaper","qty": 24}}'),
      ('{ "customer": "Chandler Bing", "items": {"product": "Toy Car","qty": 1}}'),
      ('{ "customer": "Phoebe Buffay", "items": {"product": "Toy Train","qty": 2}}');

We will use a SELECT statement to query JSON data, which is similar to querying other native data types, which will generate the following output:

SELECT info FROM goods;

When to Use JSONB

We used JSONB for:

• Product attributes that vary by category
• User preferences and settings
• Storing semi-structured data from third-party APIs

-- Product catalog with flexible attributes
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC(10,2),
    attributes JSONB
);

-- Query products with specific attributes
SELECT * FROM products 
WHERE attributes @> '{"color": "red", "size": "large"}';

Boolean Data Type

PostgreSQL gives the standard SQL type Boolean. A Boolean data type can take one of three potential values: true, false, and a third state, null, which is represented by the SQL null value.

A boolean or bool is a keyword that declares a column with the Boolean data type.

When we insert data into a Boolean column, PostgreSQL transforms it into a Boolean value

• 1, yes, y, t, true values turn to true
• 0, no, false, f values turn to false.

When we select data from a Boolean column, PostgreSQL transforms the values back e.g., t to true, f to false, and space to null.

The storage size of a boolean is 1 byte.

Name	Description	Storage Size
boolean	state true or false	1 byte

Implementation Example:

Let’s understand with the help of an example. First, create a table  item_availability to log which products are available:

CREATE TABLE item_availability (
   item_id INT PRIMARY KEY,
   available BOOLEAN NOT NULL
);

Now insert some data into the item_avaliability table. We will use various literal values for the boolean values.

INSERT INTO item_availability (item_id, available)
VALUES
   (100, TRUE),
   (200, FALSE),
   (300, 't'),
   (400, '1'),
   (500, 'y'),
   (600, 'yes'),
   (700, 'no'),
   (800, '0');

Now, use the following statement to check for the item’s availability:

SELECT *
 FROM item_availability
WHERE available = 'yes';

After executing the above query, I got the expected output as shown below.

Special Data Types

Apart from primary data types, PostgreSQL also gives various data types linked to geometric and network.

• Box: The representation of a box is a rectangular box. The storage size of the box is 32 bytes.
• macaddr: The representation of macaddr is a MAC address whose storage size is 6 bytes.
• Line: The representation of a line is an Infinite line that is not fully implemented. The storage size of a line is 32 bytes.
• Polygon: The representation of a polygon is a polygon that is similar to a closed path. The storage size of a polygon is 40+ 16n.
• Point: The representation of a point is a point on a plane. The storage size of a point is 16 bytes.
• Lseg: The representation of an Lseg is a line segment. The storage size of lseg is 32 bytes.
• inet: an IP4 and IPV6 address.

NAME	REPRESENTATION	STORAGE SIZE	DESCRIPTION
box	Rectangular box	32 bytes	((x1,y1),(x2,y2))
line	Infinite line(not fully implemented)	32 bytes	((x1,y1),(x2,y2))
point	the point on a plane	16 bytes	(x,y)
circle	Circle	24 bytes	<(x,y),r> (center point and radius)
lseg	Finite line segment	32 bytes	((x1,y1),(x2,y2))
path	Closed path(similar to polygon)	16+16n bytes	((x1,y1),…)
path	Open path	16+16n bytes	[(x1,y1)…]
polygon	Polygon(similar to closed path)	40+16n	((x1,y1),…)

Read: PostgreSQL WHERE IN

Custom Data Types with ENUM and DOMAIN

PostgreSQL allows creating custom types, which I’ve found extremely valuable for data integrity and code clarity.

ENUM Types

ENUM types define a static set of values:

Implementation Example:

-- Create an enum for order status
CREATE TYPE order_status AS ENUM (
    'pending', 
    'processing', 
    'shipped', 
    'delivered', 
    'canceled'
);

-- Use the enum in a table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    status order_status DEFAULT 'pending'
);

This prevents invalid status values and makes the intent clearer than using string constants.

DOMAIN Types

DOMAINS add constraints to existing types:

Implementation Example:

-- Create a domain for email addresses
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

-- Use the domain in a table
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email email_address
);

Network Address Type:

PostgreSQL offers data types to store MAC addresses, IPv4, and IPv6 addresses. It is recommended to use these types rather than plain text types to store network addresses, as they provide error checking and specialized operators and functions.

Name	Description	Storage size
inet	IPv4 and IPv6 hosts and networks	7 or 19 bytes
cidr	IPv4 and IPv6 networks	7 or 19 bytes
macaddr	MAC addresses	6 bytes

Read: PostgreSQL TO_NUMBER() function

Conclusion

In this PostgreSQL tutorial, we have learned about PostgreSQL Data Types. Here, we have covered various data types available in PostgreSQL, along with examples.

By thoughtfully selecting appropriate data types, you can:

• Improve database performance
• Reduce storage requirements
• Enhance data quality
• Simplify application code
• Express business rules directly in the database schema

You may also like to read the following PostgreSQL articles.

• PostgreSQL change column data type
• PostgreSQL TO_CHAR function
• How to back up a PostgreSQL database
• PostgreSQL Export Table to CSV
• PostgreSQL drop all tables
• PostgreSQL list databases