FME considers a PostGIS dataset to be a database containing a collection of relational tables together with their corresponding geometries. The tables must be defined in the mapping file before they can be read. Arbitrary WHERE clauses and joins are fully supported, as well as an entire arbitrary SQL SELECT statement; however, the user then assumes responsibility for the correctness of the statement or clause including quoting where necessary. Support for @SQL and @Relate functions has also been added.
When reading from the PostGIS/PostgreSQL database, each table is considered a feature type in FME and each row of a table at least one feature in FME. In the case of heterogeneous geometry collections, they may become more than one FME feature.
The basic reading process involves opening a connection to the database, querying metadata, and querying data. The data is read using a text cursor and rows are fetched to the client machine in batches of 10000 by default. There is one cursor per input table.
If null geometries are read, they are treated as non-geometry features and the attributes are preserved.
Table and column names are truncated at 64 characters. If duplicate names are produced by truncation, the behavior is undetermined. Please ensure that table names comply with PostgreSQL naming conventions.
Spaces and special characters are permissible in both table and column names. Case sensitivity has also been implemented, so table and column names are no longer changed to lowercase.
Table listing support when using the PostGIS settings boxes has been improved to avoid errors with schemas and tables that do not exist, or are inconsistent with the PostGIS metadata.
UNICODE support has been added to work with a client’s system encoding. Although there is no way to explicitly specify the encoding, the client is assumed to have entered data and created tables and columns in the encoding of their operating system. Multiple system encodings are now supported via the native PostgreSQL conversions between client and server, particularly if the server encoding is set to UNICODE.
Older schema directives have been removed and qualified table naming is now supported in the form <schemaname>.<tablename>. Additionally, the schema search path is now read and interpreted to determine a user’s default schema when writing and the available schema to read from when reading. Failing a valid schema search path, the default public schema will be used for newer databases.