Importing records from PostgreSQL to MySQL

mysql sql postgresql database-migration
Joseph picture Joseph · Apr 20, 2011 · Viewed 12.1k times · Source

Was wondering if anyone had any insight or recommended tools for exporting the records from a PostgreSQL database and importing them into a MySQL database. I believe the table structure is 100% identical.

Thoughts? Thanks!

Answer

Matthew Wood picture Matthew Wood · Apr 21, 2011

The command

pg_dump --data-only --column-inserts <database_name>

will generate SQL-standard-compliant INSERT statements with all column names listed and one VALUES clause per INSERT. This is the most portable way of moving data from PostgreSQL to any other SQL database.

Related questions

MySQL columns with DEFAULT NULL - stylistic choice, or is it?

In many flavors of SQL, there are three ways you can implicitly set a column to NULL on every row insertion. These are: columnname type NULL columnname type DEFAULT NULL columnname type NULL DEFAULT NULL I.e. the first one …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
Best practices for SQL varchar column length

Every time is set up a new SQL table or add a new varchar column to an existing table, I am wondering one thing: what is the best value for the length. So, lets say, you have a column called …

Read More