CODEWITHSUNDEEP
mysqlpostgresql
dennitorf
dennitorf

Reputation: 319

creating mysqldump for postgres

I am creating a mysql dump file to import in postgresql. I am using:

mysqldump -u username -p --compatible=postgresql databasename > outputfile.sql

Now, I have a problem the INSERT sentences, if there are a boolean column, the MySql dump write this value to the file as 0 or 1 (true or false), but when I import the dump with psql, this program require that the value for boolean fields be "true", or "false".

Any solution?

Upvotes: 3

Views: 1531

Answers (2)

Slobodan Pejic
Slobodan Pejic

Reputation: 552

user3182456's answer is great. However, if you do not have access to pg_cast and you don't mind altering your MySQL database, then you can convert your tinyint(1) columns to char(1) first. This will produce '0' and '1' in the dump and postgres understands those as booleans.

ALTER TABLE YourTable
MODIFY COLUMN YourTinyInt1Column CHAR(1);

Upvotes: 2

user_0
user_0

Reputation: 3363

IF your problem is only from the booleans, you can change Postgresql (not a good solution, but a temp patch).

update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype;

When you have finished:

update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype;

Source: https://dba.stackexchange.com/questions/46140/have-postgresql-accept-1-and-0-as-true-and-false-for-boolean

Upvotes: 2

Related Questions