0

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
);
1

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,
  • And varchar(60) only if there realy should be a limit, otherwise varchar or text. The same definitely applies to the present varchar(2500) column. – dezso Jul 18 '18 at 9:02
  • @dezso: varchar(60) seems to make sense as a business constraint. I would also keep the varchar(2500) assuming that the programs that use the data rely on the fact that the values will never be longer – a_horse_with_no_name Jul 18 '18 at 9:04
  • 1
    You might be totally right, but I am yet to see any real constraint on the length where it is longer than 100 characters (about). – dezso Jul 18 '18 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.