How PostgreSQL’s Hidden Data Types Can Improve Your Database Design

PostgreSQL is a powerful, open-source relational database management system that comes packed with features to handle a variety of data types. While many users are familiar with standard types like integers, strings, and dates, PostgreSQL also includes a collection of hidden or non-standard data types that are less well-known but can greatly enhance your database design. These specialized data types are perfect for handling unique use cases like storing network addresses, mac addresses, etc

In this blog post, we will explore some of the most useful and hidden data types in PostgreSQL that you can use to streamline your database operations and make your applications more efficient.

1. hstore: Key-Value Pairs in a Single Column

The hstore data type in PostgreSQL is used to store sets of key-value pairs in a single column. It is ideal for scenarios where you need to store semi-structured data or attributes that don't require a rigid schema.

Use Case: Ideal for flexible data storage, such as when dealing with attributes that might vary between rows.

How to Use hstore in PostgreSQL:

To use the hstore data type, you first need to ensure that the hstore extension is installed and enabled in your PostgreSQL installation. Here's how you can do that:

1. Navigate to the PostgreSQL source code directory: Go to the contrib folder in the PostgreSQL source code directory, which contains additional modules and extensions, including hstore.

 cd /path/to/postgresql/src/contrib/hstore

2. Build and install the hstore extension: Run the following commands to compile and install the extension:

Make

make install

3. Create the hstore extension in your database: Now that hstore is installed, you need to enable it in your PostgreSQL database by running the following command:

 CREATE EXTENSION hstore;

Once the extension is created, you can now use the hstore data type in your tables.

Example:

CREATE TABLE my_table (attributes hstore);

INSERT INTO my_table (attributes) VALUES ('"color" => "red", "size" => "M"');

2. citext: Case-Insensitive Text Data Type

The citext (case-insensitive text) data type in PostgreSQL allows you to store text data while ignoring case during comparisons. This is particularly useful for handling text-based data, such as email addresses or usernames, where case sensitivity doesn't matter.

Use Case: Ideal for applications where you want case-insensitive comparisons and sorting of text data, such as usernames, email addresses, or other identifiers.

How to Use citext in PostgreSQL:

To use the citext data type, you need to first ensure that the citext extension is installed and enabled in your PostgreSQL installation. Here's how you can do that:

Step 1: Navigate to the PostgreSQL Source Code Directory:

Go to the contrib folder in the PostgreSQL source code directory, which contains additional modules and extensions, including citext.

cd /path/to/postgresql/src/contrib/citext

Step 2: Build and Install the citext Extension:

Run the following commands to compile and install the extension:

make

make install

Step 3: Create the citext Extension in Your Database:

Once the extension is installed, you need to enable it in your PostgreSQL database by running the following command:

CREATE EXTENSION citext;

Example:

After the citext extension is enabled, you can use the citext data type in your tables.

CREATE TABLE users (
    username citext,
    email citext
);

INSERT INTO users (username, email) 

VALUES ('JohnDoe', 'john.doe@example.com');

Using citext in Queries:

Since citext is case-insensitive, you can perform case-insensitive comparisons and searches as shown below:

-- Case-insensitive search

SELECT * FROM users WHERE username = 'johndoe';  -- Matches 'JohnDoe', 'JOHNDOE', etc.

-- Case-insensitive search for email

SELECT * FROM users WHERE email = 'JOHN.DOE@EXAMPLE.COM';  -- Matches 'john.doe@example.com'

3. JSON / JSONB: Working with Flexible Data

PostgreSQL supports two types for storing JSON data: JSON and JSONB. The JSON type stores raw JSON text, while JSONB stores it in a more efficient binary format, enabling faster querying and manipulation.

Use Case: Perfect for applications that need to handle semi-structured or flexible data formats like APIs, configurations, or metadata.

CREATE TABLE my_table (data JSONB);

INSERT INTO my_table (data) VALUES ('{"name": "Shiju", "age": 21}');

4. CIDR: Storing Network Subnets

The CIDR (Classless Inter-Domain Routing) type is used to store IP addresses and network subnets in CIDR notation. It is perfect for managing network-related data in your database.

Use Case: Useful when working with network subnets, firewall rules, or IP address management.

CREATE TABLE my_networks (network CIDR);

INSERT INTO my_networks (network) VALUES ('192.168.1.0/24');

5. INET: Storing IP Addresses

The INET type in PostgreSQL is designed for storing individual IP addresses (both IPv4 and IPv6), along with optional subnet masks.

Use Case: Used for storing IP addresses for devices or clients in network-related applications.

CREATE TABLE my_ips (address INET);

INSERT INTO my_ips (address) VALUES ('192.168.1.1');

6. MACADDR: Storing MAC Addresses

The MACADDR type is used to store Media Access Control (MAC) addresses, which are unique identifiers assigned to network interfaces.

Use Case: Ideal for tracking devices in a network, such as routers, switches, or servers.

CREATE TABLE my_devices (mac MACADDR);

INSERT INTO my_devices (mac) VALUES ('00:14:22:01:23:45');

7. POINT: Representing Geometric Coordinates

The POINT type is a simple geometric data type used to store two-dimensional points (x, y).

Use Case: Ideal for spatial applications, such as mapping, location tracking, and geometric calculations.

CREATE TABLE locations (coordinates POINT);

INSERT INTO locations (coordinates) VALUES ('(10, 20)');

8. LSEG (Line Segment): Storing a Line in 2D Space

The LSEG type represents a line segment in two-dimensional space. It is part of PostgreSQL's rich support for geometric data.

Use Case: Used in applications that require calculations with line segments, such as geographical mapping or engineering design.

CREATE TABLE lines (segment LSEG);

INSERT INTO lines (segment) VALUES ('[(1,1), (3,3)]');

9. BOX: Representing a Rectangular Box

The BOX data type represents a rectangle defined by two points, allowing you to store and manipulate rectangular shapes.

Use Case: Useful for applications dealing with bounding boxes or spatial areas.

CREATE TABLE boxes (area BOX);

INSERT INTO boxes (area) VALUES ('((1,1), (4,4))');

10. PATH: Storing a Geometric Path

The PATH type allows you to store a sequence of connected points, representing a path that can be either open or closed.

Use Case: Ideal for representing routes, trails, or any sequence of connected coordinates.

CREATE TABLE paths (route PATH);

INSERT INTO paths (route) VALUES ('[(0,0), (1,1), (2,2)]');

11. POLYGON: Storing a Polygon

The POLYGON type stores a closed shape defined by multiple points. It's often used in spatial data applications.

Use Case: Ideal for applications requiring polygon-based queries, such as geographic systems or CAD software.

CREATE TABLE shapes (area POLYGON);

INSERT INTO shapes (area) VALUES ('((0,0), (4,0), (4,4), (0,4))');

PostgreSQL's hidden or non-standard data types provide tools that can enhance the design and performance of your database applications. By leveraging specialized types like hstore, citext, JSONB, and geometric types, you can efficiently handle semi-structured data, perform case-insensitive queries, manage network-related information, and work with spatial data—making your database more flexible and optimized for unique use cases.

The ability to store and query complex data types, such as network addresses, geometric shapes, and key-value pairs, enables developers to create more sophisticated and performant systems without compromising scalability or maintainability. By understanding and utilizing these hidden data types, you can take full advantage of PostgreSQL's capabilities, improving both the efficiency and flexibility of your database designs.

Incorporating these data types into your projects can help you achieve better data organization, faster queries, and enhanced functionality, making PostgreSQL a valuable tool in your database toolkit.