Menu

1 / 2

Recipe: moving a MySQL table to PostgreSQL in 5 steps (and proving nothing is missing)

DEV Community: postgres·dbSlice by Thinwork·24 minutes ago
#otTBuzKL
#devtopostgresql#devto#dev#postgresql#compare
Reading 0:00
15s threshold

Disclosure: I'm the author of dbSlice, the Windows tool used below. The routine itself works with any tool; here is how I run it when an application is rebuilt on PostgreSQL but the data still lives in MySQL, MariaDB or SQL Server.

1. Pick the goal

The wizard opens with eight goals (migrate tables, keep tables up to date, compare data, send rows to an API or queue, export to a file, generate test data, clone tables, load data). Here: Migrate tables .

2. Connect

Source (MySQL, MariaDB or SQL Server) and target (PostgreSQL).

3. Choose the tables

If a table does not exist in the target, nothing is written: the tool shows the CREATE TABLE , translated from the source, for you to review and run. Check what never translates on its own:

UNSIGNED integers: use BIGINT (or NUMERIC(20) for BIGINT UNSIGNED )

TINYINT(1) flags: declare them BOOLEAN

zero dates ( 0000-00-00 ): PostgreSQL rejects them; turn them into NULL in a SELECT used as the source

4.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.