Convert Db2 data types to PostgreSQL with the full type mapping and a catalog check for every risky column

How do I convert Db2 data types to PostgreSQL?

Extract the DDL with db2look or let a converter read the catalog, strip the Db2 storage clauses (tablespace, compression, organize by), and map the types with the table below. Db2 and PostgreSQL agree on more than most pairs: both keep empty strings apart from NULL by default, both have a calendar-only DATE, real identity columns, a native XML type and MERGE. That is why most rows of the mapping are dull. The work is in the five that are not.

Generated DDL is a draft. AWS SCT, SQLines and Ispirer all produce one you will mostly keep, and the tool comparison for this route is on our Db2 to PostgreSQL migration tools page. Create the PostgreSQL tables yourself from the corrected draft rather than letting a loader create them at load time, because a loader that creates tables is a loader applying its own defaults.

What is the PostgreSQL equivalent of Db2 DECFLOAT?

NUMERIC, with no precision declared. IBM describes DECFLOAT as decimal floating point with "either 16 or 34 digits of precision", and DECFLOAT(34) reaches exponents of plus or minus about 6,144. PostgreSQL has no decimal floating-point type at all. Its unconstrained NUMERIC stores up to 131,072 digits before the decimal point and 16,383 after, which comfortably covers every value a DECFLOAT(34) can hold, and it stores them exactly.

The tempting target is FLOAT, because the names look alike. They are not alike. PostgreSQL FLOAT with no precision is double precision, a binary type that keeps about 15 decimal digits. A value such as 0.1 has no exact binary form, so it lands as the nearest binary fraction, and a column chosen in Db2 because it had to be exact in decimal now is not. The SQLines Db2 to PostgreSQL reference lists DECFLOAT(16 | 34) as FLOAT. AWS DMS, if you let it create tables, maps DECFLOAT(16) to an 8-byte float and DECFLOAT(34) to a string, so one column sums wrong and the other does not sum at all.

Does AWS DMS replicate Db2 DECFLOAT columns?

Only in the full load. The DMS Db2 LUW source page says: "The DECFLOAT data type isn't supported. Consequently, changes to DECFLOAT columns are ignored during ongoing replication." Updates to the rest of each row still arrive, so the task stays healthy while one column quietly freezes at its snapshot value. If DMS is in the path and the schema has DECFLOAT, plan a separate reconciliation of those columns at cutover, or move them with a tool that carries them exactly.

Db2 shops that chose DECFLOAT tend to be the ones where it matters: banks, insurers and finance systems that need decimal rounding to match a regulator or a statement. Those same teams usually have to show that a migration was controlled, tested and signed off. If that evidence lives in spreadsheets today, mapping the cutover to your change-management controls before the weekend is cheaper than reconstructing it for an auditor afterwards.

What happens to Db2 timestamps with more than 6 fractional digits?

They round to six. IBM documents fractional seconds as "an attribute in the range from 0 to 12; the default is 6", and PostgreSQL says of its timestamps that "the allowed range of p is from 0 to 6." Most Db2 columns were created at the default, so they cross exactly. A column declared TIMESTAMP(9) or TIMESTAMP(12) is usually there for a reason, often as part of a key on high-frequency event or trade data, and that is exactly where rounding hurts: two values Db2 kept apart become one, and the second row fails the unique constraint. Run the two timestamp checks below before deciding.

How do I convert Db2 FOR BIT DATA columns to PostgreSQL?

Declare them as BYTEA and move them as bytes. IBM's definition is that the column contents are "treated as bit (binary) data" and that "code page conversions are not performed." Many older Db2 schemas store GUIDs, hashes, packed keys or encrypted tokens this way under a CHAR or VARCHAR type name. A tool that matches on the type name sends them to PostgreSQL text, the bytes go through a character set, and the result is either invalid byte sequence errors or, worse, values that load and no longer match the system that issued them.

Can PostgreSQL store a 2 GB Db2 BLOB?

Not in a BYTEA column. IBM allows BLOB and CLOB values "up to 2 gigabytes minus 1 byte", and PostgreSQL's maximum field size is 1 GB. Its large object facility reaches 4 TB, at the cost of a different API and separate cleanup. In practice almost every Db2 LOB is far below 1 GB and BYTEA or TEXT is right. Count the exceptions first, and if there are a handful, move those to large objects or object storage rather than redesigning the table.

How do I migrate Db2 identity columns to PostgreSQL?

Recreate them as PostgreSQL identity columns with the same ALWAYS or BY DEFAULT behavior, load the existing values, then restart each one. PostgreSQL rejects explicit values in a GENERATED ALWAYS column unless the INSERT says OVERRIDING SYSTEM VALUE, so check that your loader can send it. After the load, run ALTER TABLE t ALTER COLUMN id RESTART WITH n with n above the highest loaded value. A missed restart passes every test, then fails the first real insert after cutover on a duplicate key.

How do I verify a Db2 to PostgreSQL type conversion?

Run the checks on Db2 before the load, not on PostgreSQL after it. Most of them read the SYSCAT catalog views rather than scanning tables, so the whole list takes about an hour on a schema of any size. Replace APP with the schema, MYDB with the database, and t and col with the table and column you are checking.

After the load, reconcile on values rather than counts. For each table compare the row count, the SUM of every numeric column (run it on the DECFLOAT columns separately, and again on cutover day if DMS carried the changes), the MIN and MAX of every timestamp, and a hash of every key. Then insert one test row per table inside a transaction you roll back, to prove each identity starts in the right place. A green row-count report is the most misleading artifact in this kind of project, because it passes on a migration that turned every balance into a binary approximation.

Timing is the last piece. IBM lists 30 April 2027 as the end of base support for Db2 11.5 on Linux, UNIX and Windows, so many of these projects now have a date attached. If the move cannot happen in one weekend, PostgreSQL has to be kept current from Db2 while SQL PL is rewritten, with the same type decisions applied on every sync. The capture side is compared in change data capture tools, the other commercial sources into the same target are covered in converting T-SQL to PostgreSQL and converting PL/SQL to PostgreSQL, and the budget model for any of them is in what a data migration really costs.