Env: PB 2017 R3, postgres v13 64 bit server, ODBC driver 32bit v13
Hello Experts - We are exploring postgresql as our option as we are trying to move away from sql anywhere.
Would like to get your thoughts on what i am missing or how to go about fixing the error while retrieving existing datawindows that has tablenames as the prefix like
"Select Error: SQLSTATE = 42P01
ERROR: relation "tablename" does not exist; Error while preparing parameters"
Existing dw syntax is something like: select table.col1, table.col2 from tablee
I guess postgres prefix are schema.table.col and expects it to be like,
select schema.table.col1, schema.table.col2 from schema.table
Changing thousands existing dw's to prefix with the schema is not an easy option and honestly i think dw's should be schema agnostic and this is somethign that would ideally be provided as a setting the connection string or somwhere right?
Config:
DB server: Private schema on a private db, with a user/role that has admin access associated to the private schema. Able to select tables, DML is all good, create works... all good dba access on this schema.
ODBC DSN: uses unicode driver that has the admin user on the localhost server
PB ODBC DB profile: system tab has "PB catalog table ower: <privateschema>",
"syntax - Enclose table and col name in quotes does NOT has the check box checked and nothing filled in the "Identifier quote character"
PBODB17.ini under [PostgreSQL] has PBTableOwner set to 'YES' and PBCatalogOwner = 'private schema'
(played around with it to refer public still no luck)
I have played around with all the above options still no luck. Any help is highly appreciate!
Thanks
Balu