Conversion from Sqlite to Postgres fails when foreign_key columns are named "references"

[bjonnh]

• [X] pgloader --version

pgloader version "3.6.2" compiled with SBCL 2.1.1 ```

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

Yes: 3.6.3047c9a commit 3047c9afe141763e9e7ec05b7f2a6aa97cf06801 (2020-02-12)

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

Yes, but the issues were closed as solved (and were not from sqlite)

• [X] how can I reproduce the bug?

  Incude a self-contained pgloader command file.

pgloader this.yaml :

LOAD DATABASE
     FROM sqlite://bug_pgloader.sqlite
     INTO postgresql:///yourdb
CAST
	type real to numeric     using float-to-string
WITH
	include drop, quote identifiers, create tables, create indexes, reset sequences
SET
	work_mem to '16MB', maintenance_work_mem to '512 MB';

• [X] SQlite file:

https://www.bjonnh.net/share/bug_pgloader.sqlite

• [X] pgloader output you obtain

021-02-12T18:35:45.013333-06:00 LOG pgloader version "3.6.3047c9a"
2021-02-12T18:35:45.056666-06:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///tmp/bug_pgloader.sqlite {1007758533}>
2021-02-12T18:35:45.056666-06:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://<SNIP>@<SNIP>:<SNIP> {1007758E93}>
2021-02-12T18:35:45.770000-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "database_source" ADD FOREIGN KEY() REFERENCES "database_type"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.770000-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "organism_information" ADD FOREIGN KEY() REFERENCES "organism_database"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.770000-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "organism_information" ADD FOREIGN KEY() REFERENCES "organism_cleaned"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.770000-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "organism_synonym" ADD FOREIGN KEY() REFERENCES "organism_cleaned"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.770000-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "organism_source" ADD FOREIGN KEY() REFERENCES "organism_type"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.770000-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "reference_information" ADD FOREIGN KEY() REFERENCES "reference_database"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.770000-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "reference_information" ADD FOREIGN KEY() REFERENCES "reference_cleaned"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.770000-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "reference_source" ADD FOREIGN KEY() REFERENCES "reference_type"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_cleaned" ADD FOREIGN KEY() REFERENCES "curation_type"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_cleaned" ADD FOREIGN KEY() REFERENCES "reference_cleaned"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_cleaned" ADD FOREIGN KEY() REFERENCES "organism_cleaned"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_cleaned" ADD FOREIGN KEY() REFERENCES "structure_cleaned"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "structure_information" ADD FOREIGN KEY() REFERENCES "structure_information_type"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "structure_cleaned__structure_information" ADD FOREIGN KEY() REFERENCES "structure_information"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "structure_cleaned__structure_information" ADD FOREIGN KEY() REFERENCES "structure_cleaned"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "structure_source" ADD FOREIGN KEY() REFERENCES "structure_type"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_source" ADD FOREIGN KEY() REFERENCES "reference_source"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_source" ADD FOREIGN KEY() REFERENCES "structure_source"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_source" ADD FOREIGN KEY() REFERENCES "organism_source"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.773333-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_source" ADD FOREIGN KEY() REFERENCES "database_source"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.776666-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_source__data_cleaned" ADD FOREIGN KEY() REFERENCES "data_cleaned"() ON UPDATE NO ACTION ON DELETE NO ACTION
2021-02-12T18:35:45.776666-06:00 ERROR PostgreSQL Database error 42601: syntax error at or near ")"
QUERY: ALTER TABLE "data_source__data_cleaned" ADD FOREIGN KEY() REFERENCES "data_source"() ON UPDATE NO ACTION ON DELETE NO ACTION

[bjonnh]

I guess what I'm expecting would be something like:

ALTER TABLE "database_source" ADD FOREIGN KEY("databaseTypeId") REFERENCES "database_type" ON UPDATE NO ACTION ON DELETE NO ACTION;

instead of

 ALTER TABLE "database_source" ADD FOREIGN KEY() REFERENCES "database_type"() ON UPDATE NO ACTION ON DELETE NO ACTION

[snuggles4553]

I just stumbled upon this as well.

pgloader version: 3.6.9 (Debian 12 package)