KAHIBARO
Discord Login Register ☰
☰

11.6. PostgreSQL Data Types

Table of Contents

Why Data Types Matter in PostgreSQL

In PostgreSQL, every column, parameter, and expression has a data type. Choosing the right type:

In this chapter, you will see the most important PostgreSQL data types you will use in backend development, with many examples that match typical API and database use cases.

Rule: Always pick the most specific and correct data type for your data, not just text or varchar.

We will not cover JSON deeply here, because there is a separate chapter for JSON in PostgreSQL.

Text Types

Textual data types store strings like names, emails, descriptions, and logs.

Main string types

PostgreSQL provides several closely related text types:

TypeDescriptionLength limit stored in type?
textVariable length, practically unlimitedNo
varchar(n)Variable length, with a maximum length nYes
char(n)Fixed length, padded with spaces to length nYes

All three store strings, but there are some behavior differences.

`text`

text is the most flexible general string type:

sql 
CREATE TABLE users (
    id    serial PRIMARY KEY,
    name  text NOT NULL,
    email text NOT NULL UNIQUE
);

Use text for:

`varchar(n)`

varchar(n) enforces a maximum length at the database level:

sql 
CREATE TABLE products (
    id          serial PRIMARY KEY,
    sku         varchar(32) UNIQUE NOT NULL,
    title       varchar(255) NOT NULL,
    short_code  varchar(10)
);

If you insert a value longer than n characters, PostgreSQL will throw an error:

sql 
INSERT INTO products (sku, title)
VALUES ('ABC123', 'This title is way too long ...');  -- OK if <= 255 chars

Use varchar(n) when:

`char(n)`

char(n) is padded with spaces to the given length and is rarely needed in modern applications:

sql 
CREATE TABLE codes (
    id    serial PRIMARY KEY,
    code  char(3) NOT NULL
);

A value 'A' stored in char(3) becomes 'A ' internally.

Use char(n) only when you integrate with legacy systems that require fixed-width fields.

Practical guidance for web apps

Numeric Types

Numeric types store numbers. PostgreSQL distinguishes between:

Integer types

TypeStorageRange (approximate)
smallint2 bytes−32,768 to 32,767
integer4 bytes−2.1 billion to 2.1 billion
bigint8 bytes−9.2 quintillion to 9.2 quintillion

Typical usage:

sql 
CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    user_id     integer NOT NULL,
    item_count  integer NOT NULL CHECK (item_count >= 0),
    status      text NOT NULL
);

Use:

Auto-incrementing integers

PostgreSQL has convenience pseudo types for auto incrementing integers:

Pseudo typeReal type
serialinteger + sequence
bigserialbigint + sequence

Example:

sql 
CREATE TABLE users (
    id serial PRIMARY KEY,
    username text NOT NULL UNIQUE
);

serial creates an integer column and an associated sequence that auto increments.

Today, you will often use identity columns instead of serial:

sql 
CREATE TABLE users (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username text NOT NULL UNIQUE
);

Identity columns are the SQL standard approach and are preferable in new designs.

Floating-point types

TypeDescription
real4-byte floating point (float4)
double precision8-byte floating point (float8)

They can store very large or very small numbers, but with approximation:

sql 
CREATE TABLE measurements (
    id          serial PRIMARY KEY,
    temperature double precision,
    humidity    real
);

Floating-point numbers are not exact. That means:

sql 
SELECT 0.1 + 0.2;
-- may return 0.30000000000000004

So you must not use them for money or other exact values.

Exact numeric type: `numeric` / `decimal`

numeric (alias decimal) stores numbers with exact precision and scale.

sql 
CREATE TABLE invoices (
    id           serial PRIMARY KEY,
    total_amount numeric(10, 2) NOT NULL
);

Here:

So numeric(10, 2) allows values from -99999999.99 to 99999999.99.

Rule: Use numeric(p, s) for monetary amounts and other values that must be exact. Do not use float for prices.

Example:

sql 
INSERT INTO invoices (total_amount) VALUES (19.99);
INSERT INTO invoices (total_amount) VALUES (1000000.01);

Boolean Type

Boolean values represent truth values: true or false.

sql 
CREATE TABLE feature_flags (
    id          serial PRIMARY KEY,
    name        text NOT NULL,
    is_enabled  boolean NOT NULL DEFAULT false
);

Accepted input values for boolean include:

Value interpreted as trueValue interpreted as false
true, 't', 'yes', 'y', 'on', 1false, 'f', 'no', 'n', 'off', 0

Examples:

sql 
INSERT INTO feature_flags (name, is_enabled)
VALUES ('new_checkout', true),
       ('beta_profile', 'off');

Result:

Use boolean instead of magic values like 0/1 or 'Y'/'N' for clarity.

Date and Time Types

Time-related types are critical in backend systems, for orders, logs, sessions, and more.

Main types:

TypeStores
dateDate only (year, month, day)
time [without time zone]Time of day only
time with time zoneTime of day with time zone
timestamp without time zoneDate and time, no time zone info
timestamp with time zoneDate and time, stored in UTC internally
intervalDuration, difference between times

`date`

For values that have a date but no time or time zone:

sql 
CREATE TABLE users (
    id           serial PRIMARY KEY,
    email        text NOT NULL,
    date_of_birth date
);

Example inserts:

sql 
INSERT INTO users (email, date_of_birth)
VALUES ('a@example.com', '1990-05-10');

`timestamp with time zone` (`timestamptz`)

This is the most important type for backend systems.

timestamp with time zone is often written as timestamptz:

sql 
CREATE TABLE sessions (
    id          uuid PRIMARY KEY,
    user_id     integer NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    expires_at  timestamptz NOT NULL
);

Key points:

Example:

sql 
SET TIMEZONE = 'Europe/Berlin';
INSERT INTO sessions (id, user_id, expires_at)
VALUES ('00000000-0000-0000-0000-000000000001', 1, '2026-12-31 23:59:59+09'); -- JST
SELECT id, created_at, expires_at FROM sessions;

PostgreSQL will convert '2026-12-31 23:59:59+09' to UTC internally and show the result in Europe/Berlin local time.

Rule: For application events and logs, always use timestamp with time zone (timestamptz) and store everything in UTC.

`timestamp without time zone`

timestamp (no time zone) stores plain date and time without any offset.

Use it only when the time zone is not meaningful, for example:

You must not use it for events that happen in real time across different time zones.

`interval`

interval represents durations like "2 days" or "3 hours 15 minutes".

sql 
CREATE TABLE plans (
    id                serial PRIMARY KEY,
    name              text NOT NULL,
    trial_period      interval
);

Insert:

sql 
INSERT INTO plans (name, trial_period)
VALUES ('pro', '14 days'),
       ('enterprise', '1 month');

You can add an interval to a timestamp:

sql 
SELECT now() + interval '7 days';

You will often use interval in queries or default expressions rather than storing many intervals as columns.

UUID

UUID is a 128-bit globally unique identifier type, ideal for resource IDs in APIs.

Format example: 550e8400-e29b-41d4-a716-446655440000.

sql 
CREATE TABLE api_keys (
    id          uuid PRIMARY KEY,
    user_id     integer NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

Benefits in backend APIs:

Generating UUIDs in PostgreSQL

You need the uuid-ossp extension.

sql 
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE users (
    id   uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    email text NOT NULL UNIQUE
);

Now you can insert without specifying id:

sql 
INSERT INTO users (email) VALUES ('user@example.com');

A random version 4 UUID will be generated automatically.

You can also generate UUIDs from your backend code and send them to PostgreSQL.

Enumerated Types (ENUM)

enum types allow you to define a column that can only have one value from a predefined list.

First create the enum type:

sql 
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

Then use it in a table:

sql 
CREATE TABLE orders (
    id          serial PRIMARY KEY,
    user_id     integer NOT NULL,
    status      order_status NOT NULL DEFAULT 'pending'
);

Insert values:

sql 
INSERT INTO orders (user_id, status)
VALUES (1, 'paid'),
       (2, 'pending');

If you try to insert 'unknown' you will get an error.

Benefits:

Drawbacks:

Alternative: text column with a CHECK constraint:

sql 
CREATE TABLE orders (
    id          serial PRIMARY KEY,
    user_id     integer NOT NULL,
    status      text NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

Both approaches enforce valid values. Which you choose depends on how often you expect valid values to change.

Arrays

PostgreSQL supports array types for any base type, such as integer[], text[], uuid[].

sql 
CREATE TABLE posts (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    tags        text[]  -- array of text
);

Insert arrays:

sql 
INSERT INTO posts (title, tags)
VALUES ('Intro to PostgreSQL', ARRAY['database', 'postgres', 'sql']),
       ('Cooking Pasta', ARRAY['food', 'pasta']);

Query elements:

sql 
-- Posts that have tag 'postgres'
SELECT * FROM posts
WHERE 'postgres' = ANY (tags);

You can also use array literal syntax:

sql 
INSERT INTO posts (title, tags)
VALUES ('Travel Tips', '{travel,tips}');

Use arrays carefully:

Example of normalized alternative to tags text[]:

sql 
CREATE TABLE tags (
    id   serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);
CREATE TABLE post_tags (
    post_id integer NOT NULL REFERENCES posts(id),
    tag_id  integer NOT NULL REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

JSON and JSONB (Overview only)

PostgreSQL has powerful JSON support through json and jsonb types. There is a full chapter dedicated to JSON data, so here is just a quick overview.

TypeDescription
jsonStores JSON text exactly as given
jsonbStores JSON in binary format, efficient for queries

Example:

sql 
CREATE TABLE events (
    id         serial PRIMARY KEY,
    type       text NOT NULL,
    payload    jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO events (type, payload)
VALUES (
    'user_registered',
    '{"user_id": 123, "email": "test@example.com"}'
);

You can query inside JSONB with special operators, which you will learn in the JSON chapter.

For backend development, jsonb is usually the better choice.

Binary Data (BYTEA)

bytea stores binary data such as small files, hashes, or encrypted tokens.

sql 
CREATE TABLE files (
    id          serial PRIMARY KEY,
    filename    text NOT NULL,
    content     bytea NOT NULL
);

You usually read and write bytea from your application code using parameterized queries.

In many real applications:

But bytea is useful for:

Geometric and Network Types (Brief overview)

PostgreSQL also supports some specialized types that are very useful in specific applications.

Geometric types

For applications that work with geometric shapes:

Example:

sql 
CREATE TABLE locations (
    id    serial PRIMARY KEY,
    name  text NOT NULL,
    coord point NOT NULL
);

This is relevant for GIS or mapping applications, though in many cases you will use PostGIS, an extension, instead.

Network types

For IP and network related data:

TypeDescription
inetIPv4 or IPv6 address
cidrNetwork specification
macaddrMAC address

Example:

sql 
CREATE TABLE access_logs (
    id         bigserial PRIMARY KEY,
    user_id    integer,
    ip_address inet NOT NULL,
    accessed_at timestamptz NOT NULL DEFAULT now()
);

This is useful for logging, rate limiting, security analysis, and similar tasks.

Choosing the Right Data Type

Here are some common backend fields and recommended data types:

ConceptRecommended typeExample
User IDinteger or uuidid uuid PRIMARY KEY DEFAULT uuid_generate_v4()
Emailtext or varchar(320)email text NOT NULL
Password hashtext or byteapassword_hash text NOT NULL
Usernamevarchar(30) or text with constraintusername varchar(30) UNIQUE NOT NULL
Price/amountnumeric(10,2)price numeric(10,2) NOT NULL
Boolean flagbooleanis_active boolean NOT NULL DEFAULT true
Created / updated attimestamptzcreated_at timestamptz NOT NULL DEFAULT now()
Status fieldenum or text + checkstatus order_status NOT NULL
Tagstext[] or normalized join tabletags text[]
JSON metadatajsonbmetadata jsonb
IP addressinetip inet NOT NULL

Guideline summary:

  • Use integer / bigint for numeric IDs unless you need UUIDs.
  • Use numeric(p, s) for all monetary values.
  • Use timestamptz for timestamps in web applications.
  • Use text by default for strings, varchar(n) where a real length limit exists.
  • Use boolean for flags instead of integer or text.
  • Consider uuid for public facing resource identifiers.

Choosing good data types early helps keep your schema consistent, your data clean, and your queries efficient.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3435 / 24h
Impressum / Imprint
Datenschutz / Data Policy