PostgreSQL Column Types: A Complete Guide for Developers

Last update on December 07 2024 13:14:47 (UTC/GMT +8 hours)

PostgreSQL Column Types: A Comprehensive Guide

PostgreSQL offers a wide variety of column data types, making it one of the most versatile relational database systems. Choosing the right data type for your column ensures efficient data storage, retrieval, and manipulation.

Discription:

PostgreSQL provides built-in support for different types of data, ranging from primitive types like integers and strings to advanced types like arrays, JSON, and geometric data. Understanding these types is key to designing robust and efficient database schemas.

Common PostgreSQL Column Types

1. Numeric Types

Numeric types store numbers and are used for arithmetic operations.

Input Data

Type	Description	Example Value
smallint	2-byte integer	32767
integer	4-byte integer (default)	2147483647
bigint	8-byte integer	9223372036854775807
decimal	Arbitrary precision	12345.6789
numeric	Same as decimal	100000.99
real	4-byte floating-point number	3.14159
double precision	8-byte float	2.7182818284

Example: Numeric Column

Code:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY, -- Auto-increment integer
    salary NUMERIC(10, 2)           -- Precision: 10, Scale: 2
);

2. Character Types

These are used to store textual data.

Type	Description	Example Value
char(n)	Fixed-length	'ABC '
varchar(n)	Variable-length	'Hello'
text	Unlimited variable-length	'Long string of text'

Example: Character Column

Code:

CREATE TABLE users (
    username VARCHAR(50), -- Up to 50 characters
    bio TEXT              -- No length limit
);

3. Date/Time Types

Date and time types store temporal data.

Type	Description	Example Value
date	Date (year, month, day)	'2024-01-01'
time	Time (hour, minute, second)	'14:30:00'
timestamp	Date and time without zone	'2024-01-01 14:30:00'
timestamptz	With timezone info	'2024-01-01 14:30:00+01'

Example: Date/Time Column

Code:

CREATE TABLE meetings (
    meeting_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4. Boolean Type

The boolean type stores logical values (TRUE, FALSE, NULL).

Example: Boolean Column

Code:

CREATE TABLE features (
    feature_name TEXT,
    is_enabled BOOLEAN DEFAULT FALSE
);

5. Array Types

PostgreSQL supports arrays of any column type.

Example: Array Column

Code:

CREATE TABLE students (
    name VARCHAR(50),
    grades INTEGER[]
);

Query:

Code:


INSERT INTO students (name, grades) VALUES ('Alice', ARRAY[85, 90, 78]);

6. JSON/JSONB Types

Store structured data in JSON format.

Example: JSON Column

Code:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_details JSONB
);

Query:

Code:


INSERT INTO orders (order_details) VALUES ('{"product": "Laptop", "price": 1200}');

7. UUID Type

Stores universally unique identifiers.

Example: UUID Column

Code:

CREATE TABLE devices (
    device_id UUID DEFAULT gen_random_uuid()
);

Explanation:

Each data type in PostgreSQL serves a specific purpose:

Numeric types are ideal for calculations.
Character types store textual data efficiently.
Date/Time types help in managing schedules or timestamps.
Arrays and JSON are perfect for handling structured data without complex relationships.
UUIDs provide globally unique identifiers.

Choosing the right column type improves performance and ensures data integrity.

All PostgreSQL Questions, Answers, and Code Snippets Collection.