Use type TEXT for MySQL ENUM columns when using IMPORT FOREIGN SCHEMA

[davidrios]

I was testing the new IMPORT FOREIGN SCHEMA feature and bumped into problems with MySQL ENUMs. The way the extension presently treats them is to suggest you create a new PostgreSQL enum type named <MYSQLCOLUMNAME>_t and itself try to create the foreign tables using that type. There are 2 major problems with that approach:

1. MySQL ENUMS are table scoped, which mean they don't need to be unique across the database, the same enum name may be defined with different values for every table, unlike PostgreSQL.
2. Even if the first issue were trivial to solve, then there would be the inconvenience of having to deal with the possibly hundreds of notices this extension gives and new types that would have to be created for imported databases that make heavy use of enums.

In my opinion the current approach has no real benefit, seeing as MySQL is very forgiving with how you treat enum columns in your queries, there's no need to enforce a more strict behavior on the Postgres side. I suggest that for MySQL enum columns, the extension create the foreign table column with the TEXT type. What do you guys think?

[machytkafitanalytics]

I also agree - TEXT instead of enum works much easier....

[jjthiessen]

The current approach is my fault. I'm sorry. I was mapping MySQL ENUMs across to PostgreSQL ENUM types because it made sense in my particular use case. I was pushing everything into MATERIALIZED VIEWs (for indices and better query planning). I wanted to enforce constraints on my local PostgreSQL copies, and it was easier/cleaner for me to deal with type and value mapping at the FDW level.

For most use cases, it likely makes more sense to just use TEXT columns.

Have you tried submitting a PR for the change?

[unsa-cdn]

More than 3 years later and this is still open? I've had just that problem...

[surajkharage19]

Thanks for bringing this up. If I understand correctly, there were 2 issues reported in an initial mail by David. The first issue is already resolved by appending the table name to the enum name so that we can have a unique name for the enum. I agree that for the second issue, we need a better approach. We can think of creating the enum types implicitly with IMPORT SCHEMA command or map that to TEXT column. We will do some study around this and try to enhance this functionality. Please note that we don't have an immediate plan to fix this issue, but we will surely fix it in the near future.

[codemistake]

I encountered a similar problem. I want to import the whole database. It will be very difficult to import all tables manually. I use code like this: import foreign schema dbName from server mysql into mysql; and I also get an error on tables with 'enum'. Has anyone found a solution to this issue? The TEXT option looks absolutely working