pgdiff is finding differences on case sensitive table and column names when there isn't

[PedroSolintel]

Let's say that in DB1 I have this table:

CREATE TABLE public."Test" (
	"TestID" integer
);

And then let's say that in DB2 I have the exact same table. Pgdiff detects a difference. What's worse, it'll generate the following script:

CREATE TABLE public.Test(); and ALTER TABLE public.Test ADD COLUMN TestID integer;

Which is case insensitive and thus different than the original table. If it were to generate a script, the correct one should be:

CREATE TABLE public."Test"(); and ALTER TABLE public."Test" ADD COLUMN "TestID" integer;

[joncrlsn]

Thanks. I'll create a test to verify this and get back to you.

[joncrlsn]

Hi Pedro, I just created a test and it doesn't find any differences. However, you are correct that my generated code does not use quotes, which I will fix.

I'm wondering if you have some differences in your databases than mine. Is it possible your two databases have different encodings? I created the two databases for testing like this:

    CREATE DATABASE db1 WITH OWNER = u1 TEMPLATE = template0;
    CREATE DATABASE db2 WITH OWNER = u1 TEMPLATE = template0;

[PedroSolintel]

Interesting. I went to check the table in both databases again and the only difference between them is their ownership. In fact, my user doesn't have the permission to view the table in DB2. I then re-executed pgdiff, but now passing the admin user in the parameters, the generated script is now the following one:

DROP TABLE public.Test();

Which is odd, because the tables are still the same. I then changed the ownership in both tables to be the exactly the same one and then re-executed pgdiff. Now it got it right and didn't detect any diffs. The problem here is that changing the owner affects the diff detection on the table itself.

[joncrlsn]

I'm pretty sure the reason it found a difference is that your query needs to be done by a super-user that will be able to find all table definitions regardless of owner. I can test that theory later, but this has been a really busy week for me at work and at home.

If that turns out to be true, I'll add a warning in the documentation about that.