sql-info.de
September 19, 2006

PostgreSQL | Exporting TRUE and FALSE to MySQL

One of the many pitfalls of web-related work is that sometimes you just have to deal with MySQL, as it's the only database option some clients have. Particularly irksome is the penchant many web hosters have for older MySQL versions, which is a major PITA when trying to deal with even slightly complex data.

One of the many things lacking in MySQL is a true boolean datatype. It does possess a pseudo-type BOOL, which is silently transmuted to TINYINT(1), and from 4.1 onwards, to quote the manual, "the constants TRUE and FALSE evaluate to 1 and 0, respectively".

That makes it relatively trivial to port data containing boolean values from PostgreSQL to MySQL, because the following works:

mysql> CREATE TABLE booltest (val BOOL);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO booltest VALUES(TRUE);
Query OK, 1 row affected (0.00 sec)

Unless the target database server is MySQL 4.0.x; then it throws an error on TRUE and FALSE. Rather than messing around with converting instances of true and false in the PostgreSQL dumps (a potentially error-prone process), the following serves as a quick'n'dirty workaround:

mysql> CREATE TABLE booltest (val BOOL, true INT DEFAULT 1, false INT DEFAULT 0);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO booltest VALUES(TRUE);
Query OK, 1 row affected (0.00 sec)
Posted at 12:12 PM