PostgreSQL Data Types

PostgreSQL is one of the most feature-rich relational database systems and comes with a wide variety of built-in data types. Choosing the correct data type is crucial for performance, storage optimization, and ensuring the accuracy of data. PostgreSQL also allows you to define your own custom data types if needed.

1. Numeric Types

PostgreSQL supports several numeric types for storing whole numbers, decimals, and high-precision values:

• SMALLINT – 2 bytes, range: -32,768 to +32,767
• INTEGER (or INT) – 4 bytes, range: -2,147,483,648 to +2,147,483,647
• BIGINT – 8 bytes, range: very large whole numbers
• DECIMAL (or NUMERIC) – exact numeric with user-defined precision
• REAL – 4 bytes floating-point number
• DOUBLE PRECISION – 8 bytes floating-point number
• SERIAL – auto-incrementing integer (commonly used for primary keys)

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price DECIMAL(10,2),
  discount REAL
);

2. Character Types

Strings and text are represented using character data types:

• CHAR(n) – fixed-length string, padded with spaces if shorter
• VARCHAR(n) – variable-length string with a maximum length
• TEXT – unlimited-length text, very flexible

CREATE TABLE users (
  username VARCHAR(50) UNIQUE,
  bio TEXT
);

3. Date and Time Types

PostgreSQL provides powerful support for handling dates and times:

• DATE – calendar date (YYYY-MM-DD)
• TIME – time of day (HH:MM:SS)
• TIMESTAMP – date and time (YYYY-MM-DD HH:MM:SS)
• TIMESTAMPTZ – timestamp with time zone awareness
• INTERVAL – a span of time (e.g., 5 days, 2 hours)

CREATE TABLE events (
  event_name VARCHAR(100),
  start_time TIMESTAMP,
  duration INTERVAL
);

4. Boolean Type

The BOOLEAN type stores truth values: TRUE, FALSE, or NULL.

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100),
  completed BOOLEAN DEFAULT FALSE
);

5. Array Types

PostgreSQL allows columns to store arrays of any built-in or user-defined type.

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  grades INTEGER[]
);

-- Insert array values
INSERT INTO students (name, grades) VALUES
('Alice', '{85, 90, 92}'),
('Bob', '{70, 75, 80}');

6. JSON and JSONB

PostgreSQL is known for its excellent support for JSON data. It supports:

• JSON – stores JSON data as plain text
• JSONB – stores JSON data in a binary format (faster for queries)

CREATE TABLE api_logs (
  id SERIAL PRIMARY KEY,
  payload JSONB
);

-- Insert JSON data
INSERT INTO api_logs (payload) VALUES
('{"user": "Alice", "action": "login"}'),
('{"user": "Bob", "action": "purchase"}');

7. UUID Type

PostgreSQL has a UUID (Universally Unique Identifier) type, commonly used for globally unique primary keys.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE devices (
  id UUID DEFAULT uuid_generate_v4(),
  device_name VARCHAR(100)
);

8. Other Useful Types

• BYTEA – binary data (e.g., images, files)
• MONEY – currency amounts
• ENUM – custom enumeration type
• GEOMETRIC – points, lines, circles, polygons
• NETWORK – IP addresses (INET, CIDR, MACADDR)

-- ENUM example
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');

CREATE TABLE persons (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  current_mood mood
);

Conclusion

PostgreSQL provides a rich collection of data types to suit a wide variety of use cases. By carefully choosing the correct type, you can make your database more efficient, easier to query, and less error-prone. In the next section, we’ll learn how to insert and manipulate data effectively within PostgreSQL tables.