search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.47k stars 550 forks source link

MySQL -> Postgres: Skip COPY generated column records #1442

Open demfabris opened 2 years ago

demfabris commented 2 years ago

This is more of a how to question.

I have a couple of tables in MySQL containing generated columns. e.g.

CREATE TABLE "Foo" (
  ...
  "uniqueMD5" character(32) GENERATED ALWAYS AS (md5(coalesce("trafficSystemId",'0')::varchar || 'X' || coalesce("dbId",'0')::varchar || 'X' || coalesce("sysCode",'0')::varchar || 'X' || coalesce("zoneId",'0')::varchar || 'X' || coalesce("trafficSystemZoneId",'0')::varchar || 'X' || coalesce("name",'0')::varchar || 'X' || coalesce("isComposite",'0')::varchar || 'X' || coalesce("available",'0')::varchar)) STORED,
)

These table definitions are not properly translated when migrating to Postgres. pgloader converts it to a simple varchar type. So I thought about manually creating these tables definitions and then doing a WITH data only, create no tables load.

The issue is that I don't know if there's a way to skip COPYing these generated values, since PG will refuse to insert values in such columns.