Tags

mySQL to PostgreSQL data

4:19pm on Feb 11, 2014

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

To export:

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

To import:

1
2
3
4
5
6
7
8
9
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-00 00: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'.

Tags