Plugins being forced to use Uppercase Tablenames is causing conflicts between Postgres & Microsoft Query/Excel

[Alexander Van Vucht]

All Atlassian plugins appear to be forced to adopt uppercase names for their schema. Plugin tables appear to be generally prefixed with AO_<unique plugin code>_<plugin's table>, always in upper case. In a PostgreSQL 9.3.3 installation, when data tries to be extracted from these tables using Microsoft Query, I get "relation does not exist". This is because Microsoft Query forces the query to lower case, and Postgres is case sensitive.

EG. "SELECT * FROM public.AO_21D670_WHITELIST_RULES" gives this error:

ERROR: relation "public.ao_21d670_whitelist_rules" does not exist; Error while executing the query.

This is common to both Jira & Confluence and is likely a Marketplace-wide issue.

The general way this is fixed is by changing all the tables and columns in the Postgres database to lower case but I don't think that's an option for an Atlassian installation?

Can anyone suggest a way to get Microsoft Query working here? Is there some setting I can change in Postgres to make it case insenstive?

[Norman Abramovitz · Accepted solution]

If I remember correctly (about 15 years ago), have you tried putting the table name inside double quotes? I don't remember if the tablename parts can be individually quoted or the whole string needs to be quoted.

SELECT * FROM public."AO_21D670_WHITELIST_RULES"

They might have changed their standard to use [] characters instead like MSSQL.

[Alexander Van Vucht]

I did a packet capture and Microsoft Query strips the quotes if it can parse it. This causes Postgres to also cause the query to fail.

I had to put "LIMIT ALL" after the query for it to work. So the final query looks like this:

SELECT * FROM public."AO_21D670_WHITELIST_RULES" LIMIT ALL

Thanks though, it got me on the right track!