PostgreSQL – DDL for DB2 Tables Migration

db2-zosddlmigrationpostgresql

How would I create the DDL for PostgreSQL for the following DB2 tables?
It should be a one-to-one migration, so I would like to change only as little as possible at the schema (I have difficulties with the BLOB/CLOB datatypes and the default values).

CREATE TABLE table1
(
   field_1  DECIMAL(15)   NOT NULL,
   field_2  ROWID         NOT NULL WITH DEFAULT,
   field_3  DECIMAL(15)   NOT NULL WITH DEFAULT,
   field_4  BLOB          NOT NULL WITH DEFAULT,
   field_5  TIMESTAMP     NOT NULL WITH DEFAULT,
   field_6  INTEGER       NOT NULL WITH DEFAULT,
   field_7  VARCHAR(64)   NOT NULL WITH DEFAULT,
   field_8  SMALLINT      NOT NULL WITH DEFAULT,
   field_9  CHAR(60)      NOT NULL WITH DEFAULT,
   field_10 DATE          NOT NULL WITH DEFAULT,
   field_11 DECIMAL(12,3) NOT NULL WITH DEFAULT,
   field_12 TIME          NOT NULL WITH DEFAULT,
   field_13 BIGINT        NOT NULL WITH DEFAULT
);

Second table:

CREATE TABLE table2
(
   field_1  DECIMAL(15)     NOT NULL,
   field_2  ROWID           NOT NULL,
   field_3  VARCHAR(2500)   NOT NULL,
   field_4  CLOB
);

Best Answer

BLOB should be translated to bytea in Postgres and CLOB to text.

DECIMAL(15) is probably better done as bigint and char() should not be used at all. I would replace that with varchar(30)

I don't know what rowid is in DB2.

The with default option is invalid for Postgres. If you want a default value you need to specify it explicitly, e.g.: 

field_6  INTEGER       NOT NULL DEFAULT 42,
Related Question