ReportServer Forum

mark

Member

Registered: 2017-05-03

working with postgres schemas

Hello

My data is on greenplum DB, I'm using the following connection sting to create a new data source
jdbc:postgresql://192.168.1.41/data?searchpath=2016-data

data is the DB and 2016-data is the schema where my data is

The problem is that when I write the sql for my queries/reports if I do not use the notation schema.table or schema.table.column
Reportserver does not return any result

I also tried to specify the path in the query itself

search_path=2016-data;
select * from etc;

Again no luck

The only way to get results is to use the schema.table etc. unfortunately this add quite a bit of complexity as we have some very long and elaborated queries with many dozens of tables and hundreds of columns etc

Any idea on what I could do to be able to avoid the schema.table notation?

Thanks heaps
Mark

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: working with postgres schemas

Hi Mark,

you can also try this:
jdbc:postgresql://192.168.1.41/data?currentSchema=2016-data

If this doesn't work, please try a newer jdbc driver and let us know.

Cheers,
Eduardo

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: working with postgres schemas

Hi Mark,

this should also work:
ALTER USER user_name SET search_path to '2016-data'

Cheers,
Eduardo

mark

Member

Registered: 2017-05-03

Re: working with postgres schemas

Hello

I will try both and will update you

Thanks!

mark

Member

Registered: 2017-05-03

Re: working with postgres schemas

Hello Eduardo

Thanks it works great!

I tried many other connection strings without luck by this one does the job

you can also try this:
jdbc:postgresql://192.168.1.41/data?currentSchema=2016-data

Again thanks heaps

Mark