Geek asks and answers

Creating mysqldump for postgres

I am creating a mysql dump file for import into postgresql. I use:

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

Now I have a problem with sentences INSERT, if there is a Boolean column, the MySql dump writes this value to the file as 0 or 1 (true or false), but when I import the dump with psql, this program requires the value for the logical fields to be "true" or false.

Any solution?

+3
source share
2 answers

user3182456 the answer is great. However, if you do not have access to pg_castand do not mind changing your MySQL database, you can first convert the columns tinyint(1)to char(1). This will create '0'and '1'in the dump postgres and understand them as booleans.

ALTER TABLE YourTable
MODIFY COLUMN YourTinyInt1Column CHAR(1);
+2

, Postgresql ( , ).

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

:

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

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

+2

More articles:


All Articles