How to Convert Postgresql Boolean to MySQL Tinyint?

Question

I am trying to migrate a PostgreSQL table to a MySQL with all data, but I could not migrate because of two columns. These columns are in the Postgresql table as boolean and values in these columns are TRUE or FALSE (it looks like a string). I created a boolean column in MySQL but it doesn't accept the TRUE/FALSE data. What should I use instead of boolean for these TRUE/FALSE values? I tried tinyint(4) but it doesn't work(I must migrate the data as it is TRUE or FALSE, not t/f or 1/0).

An example:

Postgresqltable( id bigserial not null, message_listening boolean, ....... ) 
Example record: (1, TRUE, .......)

Can you show us the generated sql or csv format with an example record? — Royal Wares
– Royal Wares, Commented Aug 21, 2020 at 7:06
@AlexanderDeSousa Postgresqltable( id bigserial not null, message_listening boolean ....... ) Example row for postgresqltable: (1, TRUE, ........) — Emrah Tema
– Emrah Tema, Commented Aug 21, 2020 at 7:11

kmoser · Accepted Answer · 2020-08-21 07:10:08Z

2

Migrate them as VARCHAR(255). Then, for each of those columns you want to convert to native MySQL TINYINT(1):

Create another column (e.g. test) of type TINYINT(1)
Issue the command UPDATE tbl SET test = IF( bool_col = 'TRUE', 1, 0 ), where bool_col is the column you migrated from PostgreSQL which contains the Boolean string
Drop the bool_col column
Rename the test column to bool_col (or whatever you prefer)

Repeat the above steps for each column you want to convert.

answered Aug 21, 2020 at 7:10

kmoser

9,3693 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fredrik Over a year ago

Suppose the select used to generate the dump could incorporate that IF. Could be easier than working with temporary columns like that.

kmoser Over a year ago

That would definitely be a better choice if it's possible to re-dump the data. But not an option if the data can't be dumped again.

Collectives™ on Stack Overflow

How to Convert Postgresql Boolean to MySQL Tinyint?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related