Problems with boolean conversions - every record gets true value

[bjornbak]

I have a mixture of field types in oracle representing booleans:

• 5 character varchar2's containing text True or False (capitalized).
• 1 digit numeric containing 1 or 0.
• 3 character varchar2's containing text ja or nej (danish for yes and no).

I would like cleanup this mess during migrating and make them all into booleans.

I made a long list of fields like
REPLACE_AS_BOOLEAN periode:disabled kundeaftale:saetadressevedbooking billede:disabled virksomhedlink:disabled v_facilitetdef:gruppevisning ..................

and configured the value mapping as:
BOOLEAN_VALUES True:False 1:0 ja:nej

The fields are converted to boolean fields but in the generated copy file all the fields has a t for true.

Is this misconfiguration or a bug in ora2pg?

[darold]

This is not a misconfiguration or a bug, this is the PostgreSQL representation of boolean in COPY output. You will have t for true and f for false. In INSERT statement you will have 't' and 'f'.

Regards

[bjornbak]

Hi Darold,

Thank you for your answer but we misunderstand each other..

I understand that the t represent a true but I don't understand why ALL the rows get a t while some of them should get a f...

[darold]

OK, sorry for the misunderstanding. If REPLACE_AS_BOOLEAN is a list of table_name:column_name there's no misconfiguration. The only things is that you don't need to add 1:0 to BOOLEAN_VALUES as it is defined per default.

I don't see any bug in the code, what version of Ora2Pg are you using ?

[bjornbak]

I'm using ora2pg-11.4 with an Oracle 10.2g

I'm doing a oracle->file migration and then loading these to postgresql with psql.

Could it make a difference if I copy directly to postgresql?

Here is an example:

Oracle DDL:
CREATE TABLE "TRUNK"."AM"
( "ENHED_ID" NUMBER(10,0) CONSTRAINT "AM_ENHED_ID_C" NOT NULL ENABLE,
"ANTAL" NUMBER(5,0) DEFAULT 0 CONSTRAINT "AM_ANTAL_C" NOT NULL ENABLE,
"ANTALBOOKED" NUMBER(5,0) DEFAULT 0 CONSTRAINT "AM_ANTALBOOKED_C" NOT NULL ENABLE,
"DATO" DATE CONSTRAINT "AM_DATO_C" NOT NULL ENABLE,
"LUKKET" NUMBER(1,0) DEFAULT 0 CONSTRAINT "AM_LUKKET_C" NOT NULL ENABLE,
"BR_MINDAGE" NUMBER(3,0) DEFAULT 1 CONSTRAINT "AM_BR_MINDAGE_C" NOT NULL ENABLE,
"MANUELPRIS" NUMBER(6,2),
"BR_ANKOMSTTILLADT" NUMBER(1,0) DEFAULT 1 CONSTRAINT "AM_BR_ANKOMSTTILLADT_C" NOT NULL ENABLE,
"BR_MAXDAGE" NUMBER(3,0),
"BR_AFREJSETILLADT" NUMBER(1,0) DEFAULT 1 CONSTRAINT "AM_BR_AFREJSETILLADT_C" NOT NULL ENABLE,
CONSTRAINT "AM_PK" PRIMARY KEY ("ENHED_ID", "DATO") ENABLE,
CONSTRAINT "AM_CHK1" CHECK (
antal >= antalbooked
);

Postgresql DDL:
CREATE TABLE am
(
enhed_id integer NOT NULL,
antal integer NOT NULL DEFAULT 0,
antalbooked integer NOT NULL DEFAULT 0,
dato timestamp without time zone NOT NULL,
lukket boolean NOT NULL DEFAULT false,
br_mindage integer NOT NULL DEFAULT 1,
manuelpris real,
br_ankomsttilladt boolean NOT NULL DEFAULT true,
br_maxdage integer,
br_afrejsetilladt boolean NOT NULL DEFAULT true,
CONSTRAINT am_pkey PRIMARY KEY (enhed_id, dato),
CONSTRAINT am_chk1 CHECK (antal >= antalbooked)
)

From ora2pg:

REPLACE_AS_BOOLEAN am:br_ankomsttilladt am:br_afrejsetilladt am:lukket

Data in oracle:

"ENHED_ID" "ANTAL" "ANTALBOOKED" "DATO" "LUKKET" "BR_MINDAGE" "MANUELPRIS" "BR_ANKOMSTTILLADT" "BR_MAXDAGE" "BR_AFREJSETILLADT"
"1032" "10" "3" "01-JAN-11 00:00" "0" "1" "" "1" "" "1"
"1032" "10" "3" "02-JAN-11 00:00" "0" "1" "" "1" "" "1"
"1032" "10" "4" "03-JAN-11 00:00" "0" "1" "" "1" "" "1"
"1032" "10" "1" "04-JAN-11 00:00" "0" "1" "" "1" "" "1"
"1032" "10" "1" "05-JAN-11 00:00" "0" "1" "" "1" "" "1"
"1032" "10" "1" "06-JAN-11 00:00" "0" "1" "" "1" "" "1"
"1032" "10" "0" "07-JAN-11 00:00" "0" "1" "" "1" "" "1"
"1032" "10" "0" "08-JAN-11 00:00" "0" "1" "" "1" "" "1"
"1032" "10" "0" "09-JAN-11 00:00" "0" "1" "" "1" "" "1"

Data in copy:

COPY am (enhed_id,antal,antalbooked,dato,lukket,br_mindage,manuelpris,br_ankomsttilladt,br_maxdage,br_afrejsetilladt) FROM STDIN;
1032 10 3 2011-01-01 00:00:00 t 1 \N t \N t
1032 10 3 2011-01-02 00:00:00 t 1 \N t \N t
1032 10 4 2011-01-03 00:00:00 t 1 \N t \N t
1032 10 1 2011-01-04 00:00:00 t 1 \N t \N t
1032 10 1 2011-01-05 00:00:00 t 1 \N t \N t
1032 10 1 2011-01-06 00:00:00 t 1 \N t \N t
1032 10 0 2011-01-07 00:00:00 t 1 \N t \N t
1032 10 0 2011-01-08 00:00:00 t 1 \N t \N t
1032 10 0 2011-01-09 00:00:00 t 1 \N t \N t

As you can see "lukket" is false for all of the rows in oracle but has a t in copy.

As stated earlier I get the same result for the text fields containing True:False and ja:nej.

[darold]

Thanks a lot Christian, your example helped a lot to find the bug. It is fixed now with commit 5edc4e5. Please download latest development code and give it a try.

Best regards,

[bjornbak]

That worked :)

Thank you!