Pgloader: Column case is ignored

[chrisspen]

I converted a mysql database to postgres by running:

pgloader mysql://olduser:oldpass@oldhost/olddb postgresql://newuser:newpass@newhost/newdb

and the new postgres database is unusable by my application because the case is broken in any column name that used mixed or camel case. e.g. A column called someColumn gets turned into somecolumn, which in postgres are not the same. It appears all columns were converted to lower-case.

The help docs don't mention any option to change this. Is there any way to stop this from happening or is this a limitation of the tool?

[dimitri]

The default is to follow PosgreSQL defaults and down case every identifier. Use --with "quote identifiers" at the command line to get the behaviour you're asking for, keeping the exact case of your fields. Note that every use of those column names will have to be double-quoted when using PostgreSQL.

And yes it is documented at https://github.com/dimitri/pgloader/blob/master/pgloader.1.md#load-mysql-database

  - *downcase identifiers*

    When this option is listed, pgloader converts all MySQL identifiers
    (table names, index names, column names) to *downcase*, except for
    PostgreSQL *reserved* keywords.

    The PostgreSQL *reserved* keywords are determined dynamically by
    using the system function `pg_get_keywords()`.

  - *quote identifiers*

    When this option is listed, pgloader quotes all MySQL identifiers so
    that their case is respected. Note that you will then have to do the
    same thing in your application code queries.

[eurobob]

@dimitri @chrisspen

Sorry to reopen an old issue, but this is not working so well. When I try to add the --with option it doesn't work, no matter if I put it before the mysql & postgres urls, or after them

❯ pgloader --with "quote identifiers" mysql://root@localhost:3306/guguide pgsql://postgres@localhost:5432/guguide
#<CLOSURE (LAMBDA (COMMAND-LINE-ARGUMENTS::VALUE)
            :IN
            COMMAND-LINE-ARGUMENTS::MAKE-OPTION-ACTION) {1004DD0D4B}> fell through ETYPECASE expression.
Wanted one of (NULL KEYWORD SYMBOL CONS).
pgloader [ option ... ] command-file ...
pgloader [ option ... ] SOURCE TARGET
  --help -h                       boolean  Show usage and exit.
  --version -V                    boolean  Displays pgloader version and exit.
  --quiet -q                      boolean  Be quiet
  --verbose -v                    boolean  Be verbose
  --debug -d                      boolean  Display debug level information.
  --client-min-messages           string   Filter logs seen at the console (default: "warning")
  --log-min-messages              string   Filter logs seen in the logfile (default: "notice")
  --summary -S                    string   Filename where to copy the summary
  --root-dir -D                   string   Output root directory. (default: #P"/tmp/pgloader/")
  --upgrade-config -U             boolean  Output the command(s) corresponding to .conf file for
                                           v2.x
  --list-encodings -E             boolean  List pgloader known encodings and exit.
  --logfile -L                    string   Filename where to send the logs.
  --load-lisp-file -l             string   Read user code from files
  --dry-run                       boolean  Only check database connections, don't load anything.
  --on-error-stop                 boolean  Refrain from handling errors properly.
  --no-ssl-cert-verification      boolean  Instruct OpenSSL to bypass verifying certificates.
  --context -C                    string   Command Context Variables
  --with                          string   Load options
  --set                           string   PostgreSQL options
  --field                         string   Source file fields specification
  --cast                          string   Specific cast rules
  --type                          string   Force input source type
  --encoding                      string   Source expected encoding
  --before                        string   SQL script to run before loading the data
  --after                         string   SQL script to run after loading the data
  --self-upgrade                  string   Path to pgloader newer sources
  --regress                       boolean  Drive regression testing

❯ pgloader mysql://root@localhost:3306/guguide pgsql://postgres@localhost:5432/guguide --with "quote identifiers"
KABOOM!
FATAL error: No such file or directory: "mysql://root@localhost:3306/guguide"
   No such file or directory: "pgsql://postgres@localhost:5432/guguide"
   No such file or directory: "--with"
   No such file or directory: "quote identifiers"
An unhandled error condition has been signalled:
   No such file or directory: "mysql://root@localhost:3306/guguide"
   No such file or directory: "pgsql://postgres@localhost:5432/guguide"
   No such file or directory: "--with"
   No such file or directory: "quote identifiers"



2020-04-30T16:17:03.009000+01:00 LOG pgloader version "3.6.d5314a6"

What I am doing here?

No such file or directory: "mysql://root@localhost:3306/guguide"
   No such file or directory: "pgsql://postgres@localhost:5432/guguide"
   No such file or directory: "--with"
   No such file or directory: "quote identifiers"

[phoe]

#<CLOSURE (LAMBDA (COMMAND-LINE-ARGUMENTS::VALUE)
            :IN
            COMMAND-LINE-ARGUMENTS::MAKE-OPTION-ACTION) {1004DD0D4B}> fell through ETYPECASE expression.
Wanted one of (NULL KEYWORD SYMBOL CONS).

This is a type error that IMO shouldn't have happened. Reopening.

[pravatk]

I am also facing the same issue when running pgloader on a sqlite database. The loaded table names and columns are always in lower case.
But when I run with --with "quote identifiers", I see a different error mentioned below.

pgloader test.db postgresql:///test --with "quote identifiers"
KABOOM!
FATAL error: :UTF-8 stream decoding error on #<SB-SYS:FD-STREAM for "file <pathto>/test.db" {10050ED663}>: the octet sequence #(172) cannot be decoded.
An unhandled error condition has been signalled: :UTF-8 stream decoding error on #<SB-SYS:FD-STREAM for "file <pathto>/test.db" {10050ED663}>: the octet sequence #(172) cannot be decoded.



2020-05-06T19:05:26.012000+05:30 LOG pgloader version "3.6.2"
2020-05-06T19:05:26.014000+05:30 LOG Data errors in '/private/tmp/pgloader/'
2020-05-06T19:05:26.014000+05:30 LOG Parsing commands from file #P"<pathto>/test.db"

What I am doing here?

:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM for "file <pathto>/test.db" {10050ED663}>:

  the octet sequence #(172) cannot be decoded.

Can someone help?

[phoe]

:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM for "file /Users/pravat/work/repos/skyschedule/utility/data/skyschedule.db" {10050ED663}>:

  the octet sequence #(172) cannot be decoded.

This happens because of an UTF-8 decoding error. Is a .db file readable as UTF-8 though? I thought that SQLite databases contained binary data, at which point reading them via a character stream is an error on pgloader side.

[pravatk]

@phoe The loader works fine without the --with "quote identifiers" option though.

[phoe]

Hmmm. It seems that the issue is distinct from the one in the original post.

Please open a new issue with this bugreport. Also, if possible, please attach a minimal reproducible test case - for debugging, we'll need a .db file that generates such an error for you, as small as possible.

[dimitri]

Just tried and failed to reproduce the error with MySQL, and then the error with SQLite is explained in #1142: please use options before arguments in the command line.