Question

Postgis reader unable to read tables.


Badge +1

Im having problems to list tables when using postgis reader, but if i list them with postgresql readers it fetches all tables from any of my schemas.

The listing is inside schemas and there are both geometric tables and non geometric ones. It is able to fetch the existent schemas in database but not the the tables inside the schemas.

In Qgis i can load and see all my tables.

Any clues or ideas @paulatsafe


4 replies

Userlevel 4
Badge +25

A few things I found in a quick search of our resources:

- This article. Does it help? Or this documentation page?

- It's a known issue that we can't see tables whose schemaname is UPPER CASE

Does that help?

Badge +1

A few things I found in a quick search of our resources:

- This article. Does it help? Or this documentation page?

- It's a known issue that we can't see tables whose schemaname is UPPER CASE

Does that help?

Right, tried the first link options before without success, but not the doc page you have sent.Will try it now. keep you posted.

Thanks Mark.

Hi Jorge,

I agree that the articles should cover it, but I'll add a few thoughts:

If the PostgreSQL (i.e., non-spatial) reader sees all of the tables you care about, that suggests either (a) your tables are in schemas in your search path, or (b) you have set the "Schemas for Table Listing" option correctly.

The next most likely issue is that your spatial tables are missing the metadata rows FME uses to see that they are spatial. The way to check that is by manually entering the schema.table name(s) into the Table List text box and see if that works.

Finally, apologies for the delay seeing this - albeit only a few hours thanks to help from a colleague - it appears that my @paulatsafe account disappeared in a recent migration; I am back again as @paulnalos.

Regards,

Paul

Badge +1

Hi Jorge,

I agree that the articles should cover it, but I'll add a few thoughts:

If the PostgreSQL (i.e., non-spatial) reader sees all of the tables you care about, that suggests either (a) your tables are in schemas in your search path, or (b) you have set the "Schemas for Table Listing" option correctly.

The next most likely issue is that your spatial tables are missing the metadata rows FME uses to see that they are spatial. The way to check that is by manually entering the schema.table name(s) into the Table List text box and see if that works.

Finally, apologies for the delay seeing this - albeit only a few hours thanks to help from a colleague - it appears that my @paulatsafe account disappeared in a recent migration; I am back again as @paulnalos.

Regards,

Paul

Hello Paul and Mark, 

Thanks for the support, 

So following Robyn’s article I did altered my users
search_path to include all of my schemas.

When running show search_path im able to see them all
listed. All my schemas are lower case.

""$user", public, sources, anchor_convex, debug, geocoding, "overlay", product_tst"

When running:

select * from geometry_columns;

im able to see that my tables are registered in metadata
tables with types , srids , geometry_column , etc , …

I have granted all on tables :

GRANT ALL ON TABLE public.geometry_columns TO PUBLIC; GRANT ALL ON TABLE public.spatial_ref_sys TO PUBLIC;

 After this changes im
still not able to list any tables from any schemas with a postgis reader .

But when hardcoding the schema.table_name in the reader
parameter Table List I’m able to load data in.

Am I missing some other step here ?

Thanks again  

Reply