mySQL to PostgreSQL data

February 11, 2014 @ 16:19

I’m trying to pitch changing to PostgreSQL at work, so I had to figure this out today.

To export:

for i in table1 table2 ; do
    mysql --batch -e "SELECT * FROM $i" > $i.csv
done

To import:

for f in *.csv; do
    TABLE=${f%.*}
    tail -n +2 $f | \
    sed -e 's/\r/\\r/g' \
        -e 's/\\0//g' \
        -e 's/0000-00-0000:00:00/NULL/g'| \
    iconv -t "utf-8" -f "utf-8" -c | \
    psql -c "COPY \"$TABLE\" FROM stdin WITH NULL 'NULL'"
done

Note the sed command to remove backslash-zero - as this is an escaped dump, that will be converted into a null character, which is not allowed in a string. Also one row I saw had the “zero date” shown there - pretty sure this date never happened, so I’m calling it ‘NULL’.