PostgreSQL to DB2 Converter: Syntax Guide & Examples

Why Convert PostgreSQL to DB2?

IBM DB2 is a mainstay in enterprise environments, particularly in banking, insurance, government, and large-scale manufacturing systems. If your organization runs on DB2 infrastructure or needs to integrate with mainframe systems, migrating from PostgreSQL to DB2 LUW (Linux, Unix, Windows) may be a requirement rather than a choice.

DB2 excels in handling massive OLTP workloads, offers deep integration with IBM's ecosystem (z/OS, CICS, MQ), and provides advanced features like temporal tables, compression, and workload management. Its SQL compatibility with the SQL standard is strong, which makes many PostgreSQL queries portable with minimal changes.

However, PostgreSQL has several features and syntax patterns that do not translate directly to DB2. This guide covers every difference you will encounter during the migration process.

Key Syntax Differences

Feature	PostgreSQL	DB2
Auto-increment	SERIAL / IDENTITY	GENERATED ALWAYS AS IDENTITY
Case-insensitive LIKE	ILIKE	UPPER(col) LIKE UPPER(pattern)
Unbounded string	TEXT	VARCHAR(n) or CLOB
JSON binary	JSONB	JSON or CLOB
Array types	INT[], TEXT[]	Not supported (use tables)
String aggregation	STRING_AGG(col, ',')	LISTAGG(col, ',')
Current timestamp	NOW()	CURRENT_TIMESTAMP
Date interval	col + INTERVAL '30 days'	col + 30 DAYS
UPSERT	ON CONFLICT ... DO UPDATE	MERGE INTO ... USING
Pagination	LIMIT n OFFSET m	OFFSET m ROWS FETCH FIRST n ROWS ONLY
String concatenation	a || b	a || b or CONCAT(a, b)
RETURNING clause	INSERT ... RETURNING *	SELECT * FROM FINAL TABLE(INSERT ...)

Common Conversions with Examples

SERIAL to GENERATED ALWAYS AS IDENTITY

PostgreSQL's SERIAL is a shorthand that creates a sequence. In DB2, you use the GENERATED ALWAYS AS IDENTITY clause directly on the column definition.

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

-- DB2
CREATE TABLE users (
    id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uq_email UNIQUE (email)
);

ILIKE to UPPER() LIKE UPPER()

PostgreSQL provides ILIKE for case-insensitive pattern matching. DB2 has no ILIKE operator, so you need to normalize both sides of the comparison with UPPER() or LOWER().

-- PostgreSQL
SELECT * FROM products WHERE name ILIKE '%widget%';

-- DB2
SELECT * FROM products WHERE UPPER(name) LIKE UPPER('%widget%');

TEXT Type to VARCHAR/CLOB

PostgreSQL's TEXT type has no length limit. DB2 does not have an unbounded TEXT type. Use VARCHAR(n) for strings up to 32,672 bytes, or CLOB for larger content.

-- PostgreSQL
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL
);

-- DB2
CREATE TABLE posts (
    id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    body CLOB(1M) NOT NULL
);

STRING_AGG to LISTAGG

PostgreSQL uses STRING_AGG to concatenate grouped values. DB2 uses LISTAGG, which is part of the SQL standard.

-- PostgreSQL
SELECT department, STRING_AGG(employee_name, ', ' ORDER BY employee_name)
FROM employees
GROUP BY department;

-- DB2
SELECT department, LISTAGG(employee_name, ', ')
    WITHIN GROUP (ORDER BY employee_name)
FROM employees
GROUP BY department;

INTERVAL Arithmetic

PostgreSQL uses the INTERVAL keyword with quoted durations. DB2 uses labeled durations directly in arithmetic expressions.

-- PostgreSQL
SELECT created_at + INTERVAL '7 days' FROM orders;
SELECT created_at - INTERVAL '3 hours' FROM orders;
SELECT NOW() - INTERVAL '1 year';

-- DB2
SELECT created_at + 7 DAYS FROM orders;
SELECT created_at - 3 HOURS FROM orders;
SELECT CURRENT_TIMESTAMP - 1 YEAR FROM SYSIBM.SYSDUMMY1;

ON CONFLICT to MERGE

PostgreSQL's ON CONFLICT clause does not exist in DB2. Instead, use the standard SQL MERGE statement.

-- PostgreSQL
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

-- DB2
MERGE INTO settings AS tgt
USING (VALUES ('theme', 'dark')) AS src(key, value)
ON tgt.key = src.key
WHEN MATCHED THEN UPDATE SET tgt.value = src.value
WHEN NOT MATCHED THEN INSERT (key, value) VALUES (src.key, src.value);

LIMIT/OFFSET to FETCH FIRST

PostgreSQL uses LIMIT and OFFSET. DB2 follows the SQL standard with FETCH FIRST and OFFSET clauses.

-- PostgreSQL
SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

-- DB2
SELECT * FROM products ORDER BY price DESC
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

RETURNING to SELECT FROM FINAL TABLE

PostgreSQL allows RETURNING to get inserted or updated rows back. DB2 uses the SELECT FROM FINAL TABLE construct.

-- PostgreSQL
INSERT INTO users (email) VALUES ('user@example.com') RETURNING id, email;

-- DB2
SELECT id, email FROM FINAL TABLE (
    INSERT INTO users (email) VALUES ('user@example.com')
);

Array Types to Relational Tables

PostgreSQL supports native array columns. DB2 does not have array types for table columns. The recommended approach is to normalize arrays into separate tables.

-- PostgreSQL
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    tags TEXT[] DEFAULT '{}'
);
SELECT * FROM articles WHERE 'sql' = ANY(tags);

-- DB2
CREATE TABLE articles (
    id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TABLE article_tags (
    article_id INT NOT NULL REFERENCES articles(id),
    tag VARCHAR(100) NOT NULL
);
SELECT DISTINCT a.* FROM articles a
JOIN article_tags t ON a.id = t.article_id
WHERE t.tag = 'sql';

Data Type Mapping

PostgreSQL Type	DB2 Type	Notes
SERIAL	INT GENERATED ALWAYS AS IDENTITY	Add NOT NULL
BIGSERIAL	BIGINT GENERATED ALWAYS AS IDENTITY	For large tables
BOOLEAN	BOOLEAN	DB2 10.5+ supports native BOOLEAN
TEXT	VARCHAR(n) or CLOB	VARCHAR up to 32,672 bytes
BYTEA	BLOB	Binary data
JSONB	JSON or CLOB	DB2 11.1+ has JSON support
TIMESTAMP	TIMESTAMP	Direct mapping
TIMESTAMPTZ	TIMESTAMP	DB2 uses session timezone
DOUBLE PRECISION	DOUBLE	Same precision
REAL	REAL	4-byte float
UUID	CHAR(36)	No native UUID in DB2
TEXT[]	Separate table	Normalize to junction table
INET	VARCHAR(45)	No native IP type in DB2
INTERVAL	No direct equivalent	Use labeled durations in expressions

Step-by-Step Migration Guide

1. Export your PostgreSQL schema. Run pg_dump --schema-only your_database > schema.sql to get DDL statements.
2. Convert the schema. Replace SERIAL with INT NOT NULL GENERATED ALWAYS AS IDENTITY, TEXT with VARCHAR(n) or CLOB, BYTEA with BLOB, JSONB with JSON or CLOB. Convert array columns into separate tables. Remove PostgreSQL-specific features like CREATE TYPE for custom ENUMs (use CHECK constraints in DB2).
3. Export the data. Use pg_dump --data-only --inserts your_database > data.sql for INSERT-based export. Convert ILIKE to UPPER() LIKE UPPER(), NOW() to CURRENT_TIMESTAMP, and array literals to normalized inserts.
4. Update your queries. Replace STRING_AGG with LISTAGG, LIMIT/OFFSET with FETCH FIRST/OFFSET ROWS, ON CONFLICT with MERGE, RETURNING with SELECT FROM FINAL TABLE, and interval arithmetic with DB2 labeled durations.
5. Handle sequences and identity. PostgreSQL sequences with custom start values or increments need to be reflected in the GENERATED ALWAYS AS IDENTITY (START WITH n, INCREMENT BY m) clause in DB2.
6. Test thoroughly. Pay special attention to queries using lateral joins, recursive CTEs (DB2 supports them but syntax may differ), and regex operations (DB2 uses REGEXP_LIKE instead of ~).
7. Deploy and monitor. Run both databases in parallel during transition. Compare query results to catch edge cases around NULL handling, implicit casting, and string comparison collation.

Need help with SQL joins during your migration? Check our visual guide for syntax that works across databases. You can also review what is SQL for foundational concepts.

Convert Automatically with AI2SQL

Manually converting hundreds of queries is tedious and error-prone. With the SQL Converter tool, you paste your PostgreSQL query, select DB2 as your target database, and get the correct syntax generated automatically. No need to remember whether DB2 uses LISTAGG or STRING_AGG, or how to rewrite RETURNING clauses.

Try the converter free and convert your first query in seconds. Looking for other conversions? See PostgreSQL to MySQL. Browse all converter tools.

Frequently Asked Questions

How do I convert PostgreSQL SERIAL to DB2?

Replace SERIAL with INT NOT NULL GENERATED ALWAYS AS IDENTITY in DB2. For BIGSERIAL, use BIGINT GENERATED ALWAYS AS IDENTITY. DB2 also supports GENERATED BY DEFAULT AS IDENTITY if you need to insert explicit values occasionally.

Does DB2 support PostgreSQL's JSONB features?

DB2 LUW 11.1+ supports a JSON data type and provides JSON functions like JSON_VALUE, JSON_QUERY, and JSON_TABLE. However, it does not have the same binary-optimized storage as PostgreSQL's JSONB. For complex JSON operations, consider using DB2's SYSTOOLS.BSON2JSON and related functions, or restructure highly queried JSON fields into relational columns.

How do I handle PostgreSQL LIMIT/OFFSET in DB2?

DB2 uses FETCH FIRST n ROWS ONLY instead of LIMIT, and OFFSET n ROWS for pagination. For example, PostgreSQL's SELECT * FROM t LIMIT 10 OFFSET 20 becomes SELECT * FROM t OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY in DB2. DB2 10.5+ also supports the LIMIT syntax as an extension, but FETCH FIRST is the standard approach.