Destination Improvement: Support for case-insensitive table/column names on Postgres targets
We are attempting to use HVR to refresh data (not replicate) from Sybase to Postgres.
Tables/column names on the source Sybase database are case sensitive.
The default for Postgres table/column names is case-insensitive.
For example, "create table myTable" on Postgres results in a table called mytable which can be accessed in any case-insensitive manner such as:
select * from mytable
select * from MYTABLE
select * from myTable etc.
However, when creating the tables on the target Postgres database, HVR is double-quoting the table/column names to retain the case-sensitivity. e.g. create table dbo."myTable".
The problem with this is that any application SQL which currently references myTable needs to be re-written using double quotes when migrating to Postgres.
For example, select * from dbo.myTable will say object not found.
To work, the SQL embedded in the application would need to be changed to select * from dbo."myTable".
This increases the complexity of the migration. We would rather HVR just accepted the default of lower-case table names on the target Postgres database.
It seems there is no way to control this behavior at the location level when using a Postgres target (the CaseSensitiveNames object property doesn't exist).
The workaround is to use the basename property for a) every table and b) every column on every table, however, this is very laborious/inefficient.
In summary, the ask is to create a new property for Postgres locations which stops HVR double-quoting table/column tables (therefore allowing us to use case insensitive table/column names post-migration without changing application SQL).
-
upvoting this :-)
-
I agree, if I am a developer, will be very painful to change all my SQL/Python/Java/.. code to 'select .. from dbo."myTable"' and test each of them.
Will be very useful if we have such option to Not force to use "", then I don't need or only minimal change to my code.
High ROI for such feature if we can have.
Please sign in to leave a comment.
Comments
2 comments