Uppercase column names have problem after migration

[msuluhan]

Thanks for contributing to pgloader by reporting an issue! Reporting an issue is the only way we can solve problems, fix bugs, and improve both the software and its user experience in general.

The best bug reports follow those 3 simple steps:

1. show what you did, _I tried to migrate mysql databases to postgresql with uppoercase column names in tables. So I used quote identifiers option in pgloader input file

load database
     from mysql://root:**********@host/db
     into postgresql://pgloader:********@localhost:5433/db
 with include drop, quote identifiers, create tables, create indexes, reset sequences

  set work_mem to '16MB', maintenance_work_mem to '512 MB';

2. show the result you got,

there was no error, while migration. It looks like everything is ok

3. explain how the result is not what you expected. When I run queries with only table names, the query is working but when I try to use column names in queries both psql and php code could not find column names both uppercase and lowercase I also migrated with only default options of pgloader (different database in same cluster). In this case there is no problem column names in psql so I could access column names in both uppercase and lowercase but there are lots of code which is working with uppercase column names. So I have to migrate database with uppercase column names in tables

• [ ] pgloader --version : pgloader version "3.6.3~devel" / compiled with SBCL 2.0.1.debian ubuntu 20.04.4 LTS

  <fill pgloader version here>
  

• [ ] did you test a fresh compile from the source tree? Installed from official repository. Could not compile code, I have got strange errors.

  Compiling pgloader from sources is documented in the README, it's easy to do, and if patches are to be made to fix your bug, you're going to have to build from sources to get the fix anyway…

• [ ] did you search for other similar issues? Y

• [ ] how can I reproduce the bug? The error occurs constantly, we can try various tests in my own environment and then I could share the output

  Incude a self-contained pgloader command file.

load database
     from mysql://root:**********@host/db
     into postgresql://pgloader:********@localhost:5433/db
 with include drop, quote identifiers, create tables, create indexes, reset sequences
  set work_mem to '16MB', maintenance_work_mem to '512 MB';

If you're loading from a database, consider attaching a database dump to
your issue. For MySQL, use `mysqldump`. For SQLite, just send over your
source file, that's easy. Maybe be the one with your production data, of
course, the one with just the sample of data that allows me to reproduce
your bug.

I have to ask my customer

When using a proprietary database system as a source, consider creating
a sample database on some Cloud service or somewhere you can then give
me access to, and see my email address on my GitHub profile to send me
the credentials. Still open a public issue for tracking and as
documentation for other users.

I couldn't fully understand after this line, so I couldn't make any comments.

--
-- EDIT THIS FILE TO MATCH YOUR BUG REPORT
--

LOAD CSV
     FROM INLINE with encoding 'ascii'
     INTO postgresql:///pgloader
     TARGET TABLE jordane

     WITH truncate,
          fields terminated by '|',
          fields not enclosed,
          fields escaped by backslash-quote

      SET work_mem to '128MB',
          standard_conforming_strings to 'on'

   BEFORE LOAD DO
    $$ drop table if exists jordane; $$,
    $$ CREATE TABLE jordane
       (
         "NOM" character(20),
         "PRENOM" character(20)
       )
    $$;

BORDET|Jordane
BORDET|Audrey
LASTNAME|"opening quote
BONNIER|testprenombe~aucouptroplong
JOURDAIN|héhé¶

• [ ] pgloader output you obtain

PASTE HERE THE OUTPUT OF THE PGLOADER COMMAND

• [ ] data that is being loaded, if relevant

PASTE HERE THE DATA THAT HAS BEEN LOADED

• [ ] How the data is different from what you expected, if relevant

[Themanwithoutaplan]

FWIW in my own MySQL to Postgres migrations I've always found it best to map explicitly in the from clause.