Introduction

Data types are a fundamental aspect of database management systems. They define the nature of data that can be stored in a table's column. Using appropriate data types helps ensure data integrity, improves query performance, and enhances database efficiency. PostgreSQL, a powerful and versatile open-source object-relational database system, offers a wide range of data types to cater to various needs. Its extensive data type support makes it suitable for diverse applications, enabling seamless integration with other technologies.

This article shows you how to use PostgreSQL data types.

Prerequisites

Before you begin, ensure you've:

• A VPS server. We recommend a Digital Ocean VPS server.
• A PostgreSQL database server. Read our guides below to learn how to install PostgreSQL on your operating system:
  • How to Install PostgreSQL on Ubuntu 24.04
  • How to Install PostgreSQL on Rocky Linux 9.
• A non-root user with sudo privileges.

Set Up a Sample Database

This section walks you through the various PostgreSQL data types with examples.

1. Log in to the PostgreSQL server as the postgres user.

   CONSOLE

   $ sudo -u postgres psql
   

2. Create a database named company.

   postgresql

   CREATE DATABASE company;
   

3. Connect to the company database.

   postgresql

   \c company
   

4. Create a table named products with columns to demonstrate different data types.

   postgresql

   CREATE TABLE products (
       product_id SERIAL PRIMARY KEY,
       product_name VARCHAR(255) NOT NULL,
       quantity INTEGER NOT NULL,
       price NUMERIC(10, 2) NOT NULL,
       available BOOLEAN NOT NULL,
       created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   );
   

5. Insert sample data into the products table.

   postgresql

   INSERT INTO products (product_name, quantity, price, available) VALUES
   ('Product 1', 100, 10.99, TRUE),
   ('Product 2', 150, 15.49, FALSE),
   ('Product 3', 200, 7.99, TRUE);
   

6. Retrieve the records from the products table to confirm the data has been inserted correctly.

   postgresql

   SELECT * FROM products;
   

Common PostgreSQL Data Types

This section introduces ten common PostgreSQL data types, their limitations, and use cases.

1. INTEGER: Used to store whole numbers.

   • Limitations: Can store values from -2,147,483,648 to 2,147,483,647.
   • Use Cases: Counting items, storing age.
   postgresql

   CREATE TABLE example_integer (
       id INTEGER
   );
   

2. SERIAL: Auto-incrementing integer.

   • Limitations: Same as INTEGER.
   • Use Cases: Auto-incrementing primary keys.
   postgresql

   CREATE TABLE example_serial (
       id SERIAL PRIMARY KEY
   );
   

3. VARCHAR(n): Variable-length character type.

   • Limitations: Maximum length specified by n.
   • Use Cases: Storing names, descriptions.
   postgresql

   CREATE TABLE example_varchar (
       name VARCHAR(50)
   );
   

4. TEXT: Variable-length character type without length limit.

   • Limitations: No length restriction.
   • Use Cases: Storing large text blocks, comments.
   postgresql

   CREATE TABLE example_text (
       description TEXT
   );
   

5. BOOLEAN: Used to store boolean values.

   • Limitations: Can store TRUE or FALSE.
   • Use Cases: Flags, conditions.
   postgresql

   CREATE TABLE example_boolean (
       is_active BOOLEAN
   );
   

6. DATE: Used to store date.

   • Limitations: Stores date only.
   • Use Cases: Birthdates, event dates.
   postgresql

   CREATE TABLE example_date (
       event_date DATE
   );
   

7. TIME: Used to store time of day.

   • Limitations: Stores time only.
   • Use Cases: Appointment times, schedules.
   postgresql

   CREATE TABLE example_time (
       event_time TIME
   );
   

8. TIMESTAMP: Used to store date and time.

   • Limitations: Stores both date and time.
   • Use Cases: Timestamps for records, logs.
   postgresql

   CREATE TABLE example_timestamp (
       created_at TIMESTAMP
   );
   

9. NUMERIC(p, s): Exact numeric type with precision and scale.

   • Limitations: Precision (p) and scale (s) must be specified.
   • Use Cases: Storing financial data, prices.
   postgresql

   CREATE TABLE example_numeric (
       price NUMERIC(10, 2)
   );
   

10. ARRAY: Used to store arrays of any data type.

    • Limitations: Can store arrays of specified type.
    • Use Cases: Storing multiple values in one column.
    postgresql

    CREATE TABLE example_array (
        values INTEGER[]
    );
    

Conclusion

This guide shows you the different data types available in PostgreSQL. You learned how to log in to the PostgreSQL server, create a database, and set up a table with various columns to illustrate different data types. Additionally, you learned about ten common PostgreSQL data types, their limitations, and use cases. For further exploration, consider experimenting with more advanced data types and functions in PostgreSQL to enhance your database management skills.