Specify TARGET TABLE with database source type

[RickLeite]

I am migrating data from an MSSQL database to a PostgreSQL database using pgloader. I would like to know if it is possible to specify the target table when using the database source type.

Currently, I expect to be able to:

Specify a particular target table where the data should be loaded.

• pgloader --version

  pgloader version "3.6.3~devel"
  compiled with SBCL 2.1.11.debian
  

• did you test a fresh compile from the source tree?

• did you search for other similar issues?

• how can I reproduce the bug?

load database
     from mssql://newuser:SimpleP#ss1@localhost:1433/sourceDB
     into postgresql://newuser:simplepassword@localhost:7432/mydatabase
     TARGET TABLE "devschema"."destinationtable"

including only table names like 'sourcetable' in schema 'dbo'

ALTER schema 'dbo' rename to 'devschema'

BEFORE LOAD DO
 $$ CREATE TABLE devschema.destinationtable 
(
  employeeid int not null,
  firstname  text  NULL,
  lastname   text  NULL,
  department text  null,
  row_ingestion_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) 
 $$;

• pgloader output you obtain

pgloader version 3.6.3~devel
compiled with SBCL 2.1.11.debian
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2024-08-05T14:39:48.010000Z NOTICE Starting pgloader, log system is ready.
2024-08-05T14:39:48.010000Z INFO Starting monitor
2024-08-05T14:39:48.020000Z LOG pgloader version "3.6.3~devel"
2024-08-05T14:39:48.020000Z LOG Data errors in '/tmp/pgloader/'
2024-08-05T14:39:48.020000Z LOG Parsing commands from file #P"/home/unix_pc/projects/reliability_data_engineer/pgloader/test.load"
KABOOM!
2024-08-05T14:39:48.030000Z INFO Parsed command:
load database
     from mssql://newuser:SimpleP#ss1@localhost:1433/sourceDB
     into postgresql://newuser:simplepassword@localhost:7432/mydatabase
     TARGET TABLE "devschema"."destinationtable"

including only table names like 'sourcetable' in schema 'dbo'

ALTER schema 'dbo' rename to 'devschema'

BEFORE LOAD DO
 $$ CREATE TABLE devschema.destinationtable 
(
  employeeid int not null,
  firstname  text  NULL,
  lastname   text  NULL,
  department text  null,
  row_ingestion_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) 
 $$;
FATAL error: At

       from mssql://newuser:SimpleP#ss1@localhost:1433/sourceDB
       into postgresql://newuser:simplepassword@localhost:7432/mydatabase
  
                                                                         ^ (Line 3, Column 71, Position 147)

In context COMMAND:

While parsing COMMAND. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
  or the character ; (SEMICOLON)
  or the string "after"
  or the string "alter"
  or the string "before"
  or the string "cast"
  or the string "distribute"
  or the string "excluding"
  or the string "including"
  or the string "materialize"
  or the string "set"
  or the string "with"
Date/time: 2024-08-05-11:39!
An unhandled error condition has been signalled: At

       from mssql://newuser:SimpleP#ss1@localhost:1433/sourceDB
       into postgresql://newuser:simplepassword@localhost:7432/mydatabase
  
                                                                         ^ (Line 3, Column 71, Position 147)

In context COMMAND:

While parsing COMMAND. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
  or the character ; (SEMICOLON)
  or the string "after"
  or the string "alter"
  or the string "before"
  or the string "cast"
  or the string "distribute"
  or the string "excluding"
  or the string "including"
  or the string "materialize"
  or the string "set"
  or the string "with"


Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {1006ECC103}>
0: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SB-IMPL::STRING-OUTPUT-STREAM {1007C8F023}>)
1: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE #<ESRAP:ESRAP-PARSE-ERROR #<ESRAP::FAILED-PARSE PGLOADER.PARSER::COMMAND @147> @147 {1007C8EFD3}> :OUTPUT NIL :IF-EXISTS :APPEND :VERBOSE NIL)
2: ((FLET "H0" :IN PGLOADER::MAIN) #<ESRAP:ESRAP-PARSE-ERROR #<ESRAP::FAILED-PARSE PGLOADER.PARSER::COMMAND @147> @147 {1007C8EFD3}>)
3: (SB-KERNEL::%SIGNAL #<ESRAP:ESRAP-PARSE-ERROR #<ESRAP::FAILED-PARSE PGLOADER.PARSER::COMMAND @147> @147 {1007C8EFD3}>)
4: (ERROR ESRAP:ESRAP-PARSE-ERROR :TEXT #<(SIMPLE-ARRAY CHARACTER (536)) load database
     from mssql://newuser:SimpleP#ss1@localhost:1433/sourceDB
     into postgresql://newuser:simplepassword@localhost:7432/mydatabase
     TARGET TABLE "devschema"."destinationtable"

in... {1007C7ACDF}> :RESULT #<ESRAP::FAILED-PARSE PGLOADER.PARSER::COMMANDS @0>)
5: (ESRAP:ESRAP-PARSE-ERROR #<(SIMPLE-ARRAY CHARACTER (536)) load database
     from mssql://newuser:SimpleP#ss1@localhost:1433/sourceDB
     into postgresql://newuser:simplepassword@localhost:7432/mydatabase
     TARGET TABLE "devschema"."destinationtable"

in... {1007C7ACDF}> #<ESRAP::FAILED-PARSE PGLOADER.PARSER::COMMANDS @0>)
6: (PGLOADER.PARSER:PARSE-COMMANDS #<(SIMPLE-ARRAY CHARACTER (536)) load database
     from mssql://newuser:SimpleP#ss1@localhost:1433/sourceDB
     into postgresql://newuser:simplepassword@localhost:7432/mydatabase
     TARGET TABLE "devschema"."destinationtable"

in... {10071E082F}> :START 0 :END NIL :JUNK-ALLOWED NIL)
7: (PGLOADER.PARSER:PARSE-COMMANDS-FROM-FILE #P"/home/unix_pc/projects/reliability_data_engineer/pgloader/test.load")
8: (PGLOADER:RUN-COMMANDS #P"/home/unix_pc/projects/reliability_data_engineer/pgloader/test.load" :START-LOGGER NIL :FLUSH-SUMMARY T :SUMMARY NIL :LOG-FILENAME NIL :LOG-MIN-MESSAGES NIL :CLIENT-MIN-MESSAGES NIL)
9: (PGLOADER::PROCESS-COMMAND-FILE ("test.load") :FLUSH-SUMMARY T)
10: (PGLOADER.MONITOR::CALL-WITH-MONITOR #<FUNCTION (LAMBDA NIL :IN PGLOADER::MAIN) {1006F114BB}>)
11: (PGLOADER::MAIN ("pgloader" "--debug" "test.load"))
12: ((LAMBDA NIL :IN "/build/pgloader-84qaOu/pgloader-3.6.3/dumper-2SKVI5f7.lisp"))
13: ((FLET SB-UNIX::BODY :IN SB-IMPL::START-LISP))
14: ((FLET "WITHOUT-INTERRUPTS-BODY-3" :IN SB-IMPL::START-LISP))
15: (SB-IMPL::START-LISP)



2024-08-05T14:39:48.040000Z INFO Stopping monitor

What I am doing here?

At

       from mssql://newuser:SimpleP#ss1@localhost:1433/sourceDB
       into postgresql://newuser:simplepassword@localhost:7432/mydatabase
  
                                                                         ^ (Line 3, Column 71, Position 147)

In context COMMAND:

While parsing COMMAND. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
  or the character ; (SEMICOLON)
  or the string "after"
  or the string "alter"
  or the string "before"
  or the string "cast"
  or the string "distribute"
  or the string "excluding"
  or the string "including"
  or the string "materialize"
  or the string "set"
  or the string "with"

• data that is being loaded, if relevant

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