WHERE clause in PostGIS reader

Field names are assumed to be lower case in PostgreSQL / PostGIS, so you will have to explicitely specify them as upper case in your case, like this:

 

 

WHERE-clause:

 

"TRACTCE00"='001811'

 

 

This is assuming that the field TRACTCE00 is a varchar or equivalent (not numeric).

 

 

(FYI: In standard SQL, double quotes signifies a field name, whereas single quotes signifies a string value. They are therefore not interchangeable, as is the case in e.g. Python.)

 

 

Good luck!

 

 

David

The "LIMIT 1" appendag seems very strange. Are you sure that you haven't set "Max features to read" or something?

 

 

A WHERE-clause should normally be able to return as many features as necessary, not just limited to one...

 

 

David

Haven't set any max features to read. And to clarify:

 

If there's no limit set in the query by the operator, FME adds LIMIT 1 to it, but then it fails. If I set LIMIT 1 it works.

Hi,

 

While working on a customer support case I ran into similar issues and finally figured out how to manage these Mixed or Upper Case table names. 

 

 

In the SQLCreator you need the double quotes around the table name and any attribute names, if they are in the database as Mixed or Upper case. So this is why 

 

 

SELECT *

 

FROM public."CensusTracts"

 

WHERE  "TRACTCE00" = '001811'

 

 

has the correct syntax for the SQLCreator.

 

 

However putting the same SELECT statement into the Postgis Reader doesn't work. Here is should be

 

 

SELECT *

 

FROM public.\\"CensusTracts\\"

 

WHERE  \\"TRACTCE00\\" = '001811'

 

 

because the reader is dropping the double quotes which then leaves the names assumed to be lowercase and it can't find either the table or the attribute.

 

 

Similarly in the WHERE clause on the reader you would need

 

\\"TRACTCE00\\" = '001811'

 

 

The LIMIT 1 that FME is putting onto the clause is so that we only read the first feature to get the schema information for the table. If you really want to limit your search to only the first feature then you need to specify the LIMIT 1 clause yourself.

 

 

Hope this makes it clearer.

 

 

Regards,

 

Robyn Rennie

 

FME Desktop Support