Case insensitivity mismatch between duckdb and postgres can cause issues

[JelteF]

DuckDB considers identifiers with different casing the same even if they are quoted.

The most minimal example of this is the following schema:

CREATE TABLE a(b int);
CREATE TABLE "A"(c int);
INSERT INTO a VALUES(1);

When using postgres execution this correctly outputs:

> SELECT * FROM a, "A" as aa;
 b │ c
───┼───
(0 rows)

But when enabling duckdb execution both a and "A" are interpreted as a. Resulting in the following incorrect result:

> SELECT * FROM a, "A" as aa;
 b │ b
───┼───
 1 │ 1
(1 row)

This happens even when I set preserver_identifier_case to true on the DuckDB connection.

[wuputah]

Good find. Some related discussion here: #56 (comment), and related to #43 -- though these are not specific to case insensitivity.

[Alzavio]

This may be related, but in either of the two below, the field name cases aren't maintained in the new postgres table. Using column names in double quotes instead of a wildcard also does not resolve the issue

CREATE TABLE postgres_staging.public.{table_name} AS SELECT * FROM postgres_prod.public.{table_name}

duckdb.sql(f'CREATE TABLE {table_name} AS SELECT * FROM postgres_prod.public.{table_name}')
duckdb.sql(f'CREATE TABLE postgres_staging.public.{table_name} AS SELECT * FROM {table_name}')

[pantonis]

This shouldn't be in the long term milestone. but an important bug to fix.

[Y--]

@pantonis we'll try to address this asap.

[JelteF]

Based on some research I did this should be quite easy to address, postgres_scanner is able to do this. I'm pretty sure it's a few lines of code only. So we can use the same approach as is used there. Let's wait until #477 is merged though, to avoid any merge conflicts.

[pantonis]

We had the same issue when we were using Hydra and it was fixed quite fast I can say.

[Leo-XM-Zeng]

This seems to be solved in the current version.

We had the same issue when we were using Hydra and it was fixed quite fast I can say.

[JelteF]

If it's indeed resolved we should add some tests to make sure we don't regress again.