PostgreSQL Data Types Explained With Examples

PostgreSQL is like a Swiss Army Knife for data storage – it’s a popular open-source relational database management system (RDBMS) that can handle just about anything you throw at it. But with great power comes great responsibility, and in this case, that responsibility is choosing the right data type. 

Fear not! In this article, we’ll explore the world of PostgreSQL data types and help you navigate the labyrinth of syntax and usage. We’ll include examples for each data type and real-world use cases. By the end, you’ll be armed with the knowledge to make informed decisions when designing your database schema.

Postgres Numeric Data Types

PostgreSQL provides several data types for storing numeric data, including but not limited to integers, floating-point numbers, decimals, and serial numbers.

Integer data types

The most commonly used integer types are:

1. SMALLINT: This is a 2-byte signed integer, with a range of -32768 to +32767.
2. INTEGER: This is a 4-byte signed integer, with a range of -2147483648 to +2147483647.
3. BIGINT: This is an 8-byte signed integer, with a range of -9223372036854775808 to +9223372036854775807.

Here’s an example of creating a table with an INTEGER column:

plaintext
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  salary NUMERIC(10, 2)
);

Floating-point data types

PostgreSQL provides two floating-point data types: FLOAT and DOUBLE PRECISION. These types are used to store numbers with a fractional part. FLOAT is a 4-byte floating-point number, while DOUBLE PRECISION is an 8-byte floating-point number.

Here’s an example of creating a table with a DOUBLE PRECISION column:

plaintext
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  price DOUBLE PRECISION
);

Decimal data types

There are two decimal data types: NUMERIC and DECIMAL. These types are used to store numbers with a fixed number of digits before and after the decimal point. The difference between the two types is that DECIMAL is an alias for NUMERIC, but with different default precision and scale values.

Here’s an example of creating a table with a NUMERIC column:

plaintext
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer TEXT,
  amount NUMERIC(10, 2)
);

Serial data types

Serial is a shorthand notation for creating an auto-incrementing integer column. When you create a column with the SERIAL data type, PostgreSQL will automatically create a sequence object to generate the next value for the column.

Here’s an example of creating a table with a SERIAL column:

plaintext
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT,
  password TEXT
);

Use Case:

A financial company is building a database to store customer account information, including account numbers, balances, and transaction history. The database will be used by employees to view and manage customer accounts, as well as by automated systems to perform account transactions.

To store this data, the company creates a table called “accounts” with the following columns:

plaintext
CREATE TABLE accounts (
  account_number INTEGER PRIMARY KEY,
  balance NUMERIC(10, 2),
  last_transaction TIMESTAMP
);

In this example, the account_number column is defined as an INTEGER, which means it can store a whole number between -2147483648 and +2147483647. This is sufficient for storing unique account numbers.

The balance column is defined as NUMERIC(10, 2), which means it can store a number with up to 10 digits, 2 of which are after the decimal point. This allows for precise tracking of account balances, which may include fractions of a cent.

Finally, the last_transaction column is defined as a TIMESTAMP, which allows for storing the date and time of the most recent transaction on the account. This data type can be used to sort and filter accounts based on transaction history, as well as to monitor account activity for fraud detection.

By using appropriate numeric data types in the database schema, the financial company can ensure that account information is accurately and efficiently stored, retrieved, and processed.

Postgres Character Data Types

There are several data types for handling character strings, which are used to represent text data. The three most commonly used character data types in PostgreSQL are the char, varchar, and text data types.

Char data type

The char data type stores fixed-length character strings. When defining a column with the char data type, you must specify the length of the string.

For example, to create a table with a column named “last_name” of type char(20):

plaintext
CREATE TABLE employees (
  employee_id serial PRIMARY KEY,
  last_name char(20),
  first_name varchar(20),
  hire_date date
);

Varchar data type

The varchar data type stores variable-length character strings. When defining a column with the varchar data type, you must specify the maximum length of the string. For example, to create a table with a column named “email” of type varchar(50):

plaintext
CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  first_name varchar(20),
  last_name varchar(20),
  email varchar(50),
  phone varchar(15)
);

Text data type

The text data type stores long strings of text with no specified length limit. For example, to create a table with a column named “description” of type text:

plaintext
CREATE TABLE products (
  product_id serial PRIMARY KEY,
  product_name varchar(50),
  description text,
  price decimal(8,2)
);

Postgres Date/Time Data Types

PostgreSQL provides several data types for working with date and time data. These data types include the date, time, timestamp, and interval.

Date data type

The date data type is used to store dates in the format of YYYY-MM-DD. For example, to create a table with a column named “order_date” of type date:

plaintext
CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  order_date date,
  customer_id int,
  product_id int,
  quantity int,
  total decimal(8,2)
);

Time data type

The time data type stores the time of day in the format of HH:MI:SS. For example, to create a table with a column named “checkin_time” of type time:

plaintext
CREATE TABLE visitors (
  visitor_id serial PRIMARY KEY,
  first_name varchar(20),
  last_name varchar(20),
  checkin_time time,
  checkout_time time
);

Timestamp data type

The timestamp data type stores both date and time information in the format of YYYY-MM-DD HH:MI:SS. For example, to create a table with a column named “created_at” of type timestamp:

plaintext
CREATE TABLE comments (
  comment_id serial PRIMARY KEY,
  post_id int,
  user_id int,
  comment_text text,
  created_at timestamp
);

Interval data type

The interval data type stores a period of time in a range of formats such as years, months, days, hours, minutes, and seconds. For example, to create a table with a column named “duration” of type interval:

plaintext
CREATE TABLE tasks (
  task_id serial PRIMARY KEY,
  task_name varchar(50),
  start_time timestamp,
  end_time timestamp,
  duration interval
);

Use Case:

Suppose you are building a website that allows users to book appointments with a healthcare provider. You need to create a database to store appointment information, including the appointment date and time. In this case, you could use the date and time data types to store the date and time of the appointment. Here’s an example of how you could create a table to store this information:

plaintext
CREATE TABLE appointments (
  appointment_id serial PRIMARY KEY,
  patient_name varchar(50),
  doctor_name varchar(50),
  appointment_date date,
  appointment_time time
);

In this example, the “appointments” table has columns for the appointment ID, patient name, doctor name, appointment date, and appointment time. The “appointment_date” column is defined as type date, while the “appointment_time” column is defined as type time.

When a patient schedules an appointment, you can insert a new record into the “appointments” table with the appropriate values for each column. For example:

plaintext
INSERT INTO appointments (patient_name, doctor_name, appointment_date, appointment_time)
VALUES ('John Smith', 'Dr. Johnson', '2023-03-10', '14:00:00');

This will create a new appointment record with the patient name “John Smith”, the doctor name “Dr. Johnson”, and the appointment date and time set to March 10th, 2023 at 2:00 PM.

The date and time data types provide a powerful and flexible way to store and manipulate date and time information in your database. Whether you’re building a healthcare appointment system or a scheduling application for any other industry, these data types are essential for managing time-related data in your database.

Postgres Boolean Data Type

This is a simple data type that has only two possible values: TRUE or FALSE. It is useful in many situations where only two states are required, such as to represent the state of a flag or the result of a logical operation.

Here is an example of creating a table with a Boolean column:

plaintext
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL,
  order_date DATE NOT NULL,
  shipped BOOLEAN DEFAULT FALSE
);

In this example, the “shipped” column is of type BOOLEAN and has a default value of FALSE.

A use case of the Boolean data type would be to keep track of the status of an order. When an order is created, the “shipped” column would be set to FALSE. Once the order has been shipped, the column can be updated to TRUE.

Postgres Array Data Type

The data type array allows you to store multiple values of the same data type in a single column. This can be useful in situations where you need to store a list of values, such as a list of phone numbers or email addresses.

Here is an example of creating a table with an array column:

plaintext
CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  emails TEXT[] NOT NULL
);

In this example, the “emails” column is of type TEXT[] (an array of text values).

A practical use case of the array data type would be to store a list of email addresses for a user. Instead of creating a separate row for each email address, you can store all the email addresses in a single column as an array.

Postgres Composite Data Types

With composite data types, you can define your own data types by combining multiple data types into a single data type. This is useful when you need to store multiple related values in a single column.

Here is an example of creating a composite data type:

plaintext
CREATE TYPE address AS (
  street VARCHAR(50),
  city VARCHAR(50),
  state CHAR(2),
  zip VARCHAR(10)
);

In this example, we have defined a new data type called “address” that consists of four fields: street, city, state, and zip.

We can then use this data type in a table definition:

plaintext
CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  address address
);

In this example, the “address” column is of type “address” (our custom composite data type).

A use case of composite data types would be to store a customer’s address as a single value instead of breaking it up into separate columns.

Special Postgres Data Types

PostgreSQL also provides several special data types that are not commonly found in other databases, such as UUID, JSON, XML, and Hstore.

UUID data type

The UUID (Universally Unique Identifier) data type allows you to store unique identifiers as 128-bit values. This can be useful when you need to generate unique identifiers for records in a table.

Here is an example of creating a table with a UUID column:

plaintext
CREATE TABLE users (
  user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(50) NOT NULL
);

In this example, the “user_id” column is of type UUID and has a default value of uuid_generate_v4(), which generates a new UUID value for each new record.

JSON data type

The JSON data type allows you to store JSON (JavaScript Object Notation) data in a column. This can be useful when you need to store complex data structures that can be easily serialized and deserialized.

Here is an example of creating a table with a JSON column:

plaintext
CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  attributes JSON NOT NULL
);

In this example, the “attributes” column is of type JSON and stores a JSON object containing various attributes for the product.

XML data type

The XML data type allows you to store XML (Extensible Markup Language) data in a column. This can be useful when you need to store and query XML data in your database.

Here is an example of creating a table with an XML column:

plaintext
CREATE TABLE books (
  book_id SERIAL PRIMARY KEY,
  title VARCHAR(50) NOT NULL,
  content XML NOT NULL
);

A practical use case of the XML data type would be to store and search books that are available in XML format.

Hstore data type

The Hstore data type allows you to store key-value pairs in a single column. This can be useful when you need to store a variable number of attributes for a record.

Here is an example of creating a table with an Hstore column:

plaintext
CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  attributes HSTORE NOT NULL
);

In this example, the “attributes” column is of type Hstore and stores a set of key-value pairs representing the attributes of the product.

A practical use case of the Hstore data type would be to store product attributes for an online store where the attributes vary between different types of products. By using an Hstore column, you can store only the relevant attributes for each product type.

Conclusion

PostgreSQL data types provide a wide range of options for storing and manipulating data. By selecting the appropriate data type, you can ensure data accuracy, consistency, and efficiency. With a better understanding of PostgreSQL’s data types, you can make informed decisions on how to best manage your data.

Learn more about PostgreSQL on the blog:

• The complete guide to PostgreSQL change data capture
• Creating a real-time materialized view with Postgres
• Stream data from Postgres to ElasticSearch

FAQs

1. What's the key difference in precision and storage size between the FLOAT and DOUBLE PRECISION data types in PostgreSQL?

Both FLOAT and DOUBLE PRECISION are used to store floating-point numbers in PostgreSQL, but they differ in precision and storage size. FLOAT uses 4 bytes and provides less precision, while DOUBLE PRECISION uses 8 bytes and offers higher precision for calculations involving decimal numbers. Choose FLOAT for general cases where approximate values are sufficient, and DOUBLE PRECISION when precision is critical, like in scientific or financial applications.

2. How can I store network addresses or geometric data in PostgreSQL?

PostgreSQL includes special data types for handling network and geometric information. For network addresses, the INET type stores IPv4 or IPv6 addresses, while CIDR represents network ranges. The MACADDR type is used for MAC addresses. For geometric data, PostgreSQL provides types like POINT (a single point), LINE, LSEG (line segment), BOX (rectangle), PATH, POLYGON, and CIRCLE, allowing you to represent various geometric shapes and perform calculations on them directly within the database.