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
