I have a Mariab-database using collation utf8bm4_general_ci and I am migrating to Postgresql.

Some tables are foreign keyed using case-insensitive strings (e-mails sourced from different systems).

When I use pgloader to migrate, I get an error message stating that some keys are not present. I can update the source table to be able to do the migration, but the error will present itself after a while if I do the switch.

What can I do to fix this other than rewriting and debugging all systems to make sure e-mails are lowercased?

  • 2
    Collation Support
    – Akina
    Jan 11 at 10:29
  • Thank you, i have read about it and an option is to create a non-deterministic collation. The downside is that it breaks queries with 'LIKE'. An option is to cast the relevant columns to CITEXT but i cannot find out how to do this for columns with specific names.
    – jared
    Jan 11 at 13:40
  • 1
    You may specify the collation in the expression, like WHERE column = 'literal' COLLATE "collation"
    – Akina
    Jan 11 at 13:45


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.