Licensing options for this format begin with FME Professional Edition.
Overview
PostgreSQL is an Object-Relational Database Management System (ORDBMS) that stores attribute information. The PostgreSQL reader/writer module enables FME to read and write PostgreSQL attribute data.
The PostgreSQL reader/writer is specifically designed to handle the attribute portion of the data in the database. When reading geometric or Spatial Reference System (SRS) data stored in a PostGIS layer over PostgreSQL, the PostGIS reader/writer module should be used instead. The PostgreSQL reader/writer communicates directly with the PostgreSQL libpq interface for maximum throughput.
This chapter assumes familiarity with PostgreSQL, SQL, the attribute types supported, and its indexing mechanisms.
For more information, please see the PostgreSQL home at http://www.postgresql.org/
Reader Overview
FME considers a PostgreSQL dataset to be a database containing a collection of relational tables together with their corresponding geometries. The tables must be defined in the workspace 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.
When reading from the PostgreSQL database, each table is considered a feature type in FME and each row of a table at least one feature in FME.
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.
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.
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 supported via the native PostgreSQL conversions between client and server, particularly if the server encoding is set to UNICODE.
Qualified table naming is supported in the form <schemaname>.<tablename>. The schema search path is 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 is used for newer databases.
Writer Overview
The PostgreSQL writer module stores both geometry and attributes into an PostgreSQL database. Note that attributes are always written as lowercase. The PostgreSQL writer provides the following capabilities:
- Transaction Support: The PostgreSQL writer provides transaction support that eases the data loading process. Occasionally, a data load operation terminates prematurely due to data difficulties. The transaction support provides a mechanism for reloading corrected data without data loss or duplication.
- Index Creation: The PostgreSQL writer can set up and populate indexes as part of the loading process. By default, no indexes are created. Additional columns can be individually indexed. Composite column indexes are not supported at this time.
- Bulk Loading: The PostgreSQL writer uses a bulk loading technique to ensure speedy data load.