View Revisions: Issue #3844

Back to Issue
Summary 0003844: "Failed to execute: CREATE TABLE" in PostgreSQL 9.6 (BIT data type with default)
Revision 2019-01-03 16:47 by lukasz.krawczyk
Description Trying to replicate single database table from MSSQL Standard 2017 to PostgreSQL 9.6 with table definition creation in PostgreSQL.

MSSQL table definition:

CREATE TABLE [dbo].[vat_rate](
    [id] [int] NOT NULL,
    [name] [varchar](64) NOT NULL,
    [value] [decimal](5, 4) NULL,
    [VAT] [bit] NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [LAST_UPDATE_DATE_TIME] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_vat_rate_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Selected master engine properties:
auto.reload=true
initial.load.create.first=true

The problem is with "[VAT] [bit] NOT NUL"L column conversion.

Exception:
2019-01-03 17:16:42,464 INFO [zapaonline-001] [DefaultDatabaseWriter] [zapaonline-001-dataloader-3] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="vat_rate">
        <column name="id" primaryKey="true" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2008" type="int" size="10"/>
        </column>
        <column name="name" required="true" type="VARCHAR" size="64">
            <platform-column name="mssql2008" type="varchar" size="64"/>
        </column>
        <column name="value" type="DECIMAL" size="5,4">
            <platform-column name="mssql2008" type="decimal" size="5" decimalDigits="4"/>
        </column>
        <column name="VAT" required="true" type="BIT" size="1" default="1">
            <platform-column name="mssql2008" type="bit" size="1" default="1"/>
        </column>
        <column name="GUID" required="true" type="CHAR" size="36" default="newid()">
            <platform-column name="mssql2008" type="uniqueidentifier" default="newid()"/>
        </column>
        <column name="LAST_UPDATE_DATE_TIME" required="true" type="TIMESTAMP" size="27">
            <platform-column name="mssql2008" type="datetime2"/>
        </column>
    </table>
</database>
2019-01-03 17:16:42,503 INFO [zapaonline-001] [PostgreSqlDatabasePlatform] [zapaonline-001-dataloader-3] Running alter sql:
CREATE TABLE "vat_rate"(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(64) NOT NULL,
    "value" NUMERIC(5,4),
    "vat" BOOLEAN DEFAULT 1 NOT NULL,
    "guid" CHAR(36) DEFAULT 'newid()' NOT NULL,
    "last_update_date_time" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id")
);

2019-01-03 17:16:42,520 WARN [zapaonline-001] [JdbcSqlTemplate] [zapaonline-001-dataloader-3] BŁĄD: kolumna "vat" jest typu boolean ale domyślne wyrażenie jest typu integer
  Hint: Będziesz musiał przepisać lub rzutować wyrażenie.. Failed to execute: CREATE TABLE "vat_rate"(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(64) NOT NULL,
    "value" NUMERIC(5,4),
    "vat" BOOLEAN DEFAULT 1 NOT NULL,
    "guid" CHAR(36) DEFAULT 'newid()' NOT NULL,
    "last_update_date_time" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id")
)
2019-01-03 17:16:42,532 ERROR [zapaonline-001] [DefaultDatabaseWriter] [zapaonline-001-dataloader-3] Failed to alter table using the following xml: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="vat_rate">
        <column name="id" primaryKey="true" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2008" type="int" size="10"/>
        </column>
        <column name="name" required="true" type="VARCHAR" size="64">
            <platform-column name="mssql2008" type="varchar" size="64"/>
        </column>
        <column name="value" type="DECIMAL" size="5,4">
            <platform-column name="mssql2008" type="decimal" size="5" decimalDigits="4"/>
        </column>
        <column name="VAT" required="true" type="BIT" size="1" default="1">
            <platform-column name="mssql2008" type="bit" size="1" default="1"/>
        </column>
        <column name="GUID" required="true" type="CHAR" size="36" default="newid()">
            <platform-column name="mssql2008" type="uniqueidentifier" default="newid()"/>
        </column>
        <column name="LAST_UPDATE_DATE_TIME" required="true" type="TIMESTAMP" size="27">
            <platform-column name="mssql2008" type="datetime2"/>
        </column>
    </table>
</database> StackTraceKey [SqlException:4238718066]
2019-01-03 17:16:42,658 ERROR [zapaonline-001] [ManageIncomingBatchListener] [zapaonline-001-dataloader-3] Failed to load batch 000-4 StackTraceKey [SqlException:4238718066]
2019-01-03 17:16:42,759 ERROR [zol-000] [AcknowledgeService] [qtp973576304-15] The outgoing batch 001-4 failed: BŁĄD: kolumna "vat" jest typu boolean ale domyślne wyrażenie jest typu integer
  Hint: Będziesz musiał przepisać lub rzutować wyrażenie.

Full log attached.
Revision 2019-01-14 14:05 by elong
Description Trying to replicate single database table from MSSQL Standard 2017 to PostgreSQL 9.6 with table definition creation in PostgreSQL.

MSSQL table definition:

CREATE TABLE [dbo].[vat_rate](
    [id] [int] NOT NULL,
    [name] [varchar](64) NOT NULL,
    [value] [decimal](5, 4) NULL,
    [VAT] [bit] NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [LAST_UPDATE_DATE_TIME] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_vat_rate_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Selected master engine properties:
auto.reload=true
initial.load.create.first=true

The problem is with "[VAT] [bit] NOT NUL"L column conversion.

Exception:
2019-01-03 17:16:42,464 INFO [zapaonline-001] [DefaultDatabaseWriter] [zapaonline-001-dataloader-3] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="vat_rate">
        <column name="id" primaryKey="true" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2008" type="int" size="10"/>
        </column>
        <column name="name" required="true" type="VARCHAR" size="64">
            <platform-column name="mssql2008" type="varchar" size="64"/>
        </column>
        <column name="value" type="DECIMAL" size="5,4">
            <platform-column name="mssql2008" type="decimal" size="5" decimalDigits="4"/>
        </column>
        <column name="VAT" required="true" type="BIT" size="1" default="1">
            <platform-column name="mssql2008" type="bit" size="1" default="1"/>
        </column>
        <column name="GUID" required="true" type="CHAR" size="36" default="newid()">
            <platform-column name="mssql2008" type="uniqueidentifier" default="newid()"/>
        </column>
        <column name="LAST_UPDATE_DATE_TIME" required="true" type="TIMESTAMP" size="27">
            <platform-column name="mssql2008" type="datetime2"/>
        </column>
    </table>
</database>
2019-01-03 17:16:42,503 INFO [zapaonline-001] [PostgreSqlDatabasePlatform] [zapaonline-001-dataloader-3] Running alter sql:
CREATE TABLE "vat_rate"(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(64) NOT NULL,
    "value" NUMERIC(5,4),
    "vat" BOOLEAN DEFAULT 1 NOT NULL,
    "guid" CHAR(36) DEFAULT 'newid()' NOT NULL,
    "last_update_date_time" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id")
);

2019-01-03 17:16:42,520 WARN [zapaonline-001] [JdbcSqlTemplate] [zapaonline-001-dataloader-3] BŁĄD: kolumna "vat" jest typu boolean ale domyślne wyrażenie jest typu integer
  Hint: Będziesz musiał przepisać lub rzutować wyrażenie.. Failed to execute: CREATE TABLE "vat_rate"(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(64) NOT NULL,
    "value" NUMERIC(5,4),
    "vat" BOOLEAN DEFAULT 1 NOT NULL,
    "guid" CHAR(36) DEFAULT 'newid()' NOT NULL,
    "last_update_date_time" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id")
)
2019-01-03 17:16:42,532 ERROR [zapaonline-001] [DefaultDatabaseWriter] [zapaonline-001-dataloader-3] Failed to alter table using the following xml: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="vat_rate">
        <column name="id" primaryKey="true" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2008" type="int" size="10"/>
        </column>
        <column name="name" required="true" type="VARCHAR" size="64">
            <platform-column name="mssql2008" type="varchar" size="64"/>
        </column>
        <column name="value" type="DECIMAL" size="5,4">
            <platform-column name="mssql2008" type="decimal" size="5" decimalDigits="4"/>
        </column>
        <column name="VAT" required="true" type="BIT" size="1" default="1">
            <platform-column name="mssql2008" type="bit" size="1" default="1"/>
        </column>
        <column name="GUID" required="true" type="CHAR" size="36" default="newid()">
            <platform-column name="mssql2008" type="uniqueidentifier" default="newid()"/>
        </column>
        <column name="LAST_UPDATE_DATE_TIME" required="true" type="TIMESTAMP" size="27">
            <platform-column name="mssql2008" type="datetime2"/>
        </column>
    </table>
</database> StackTraceKey [SqlException:4238718066]
2019-01-03 17:16:42,658 ERROR [zapaonline-001] [ManageIncomingBatchListener] [zapaonline-001-dataloader-3] Failed to load batch 000-4 StackTraceKey [SqlException:4238718066]
2019-01-03 17:16:42,759 ERROR [zol-000] [AcknowledgeService] [qtp973576304-15] The outgoing batch 001-4 failed: BŁĄD: kolumna "vat" jest typu boolean ale domyślne wyrażenie jest typu integer
  Hint: Będziesz musiał przepisać lub rzutować wyrażenie.

Full log attached.