PostgreSQL Reader/Writer
FME can read and write PostgreSQL attribute data. PostgreSQL is an Object-Relational Database Management System (ORDBMS) that stores attribute information.
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/
PostgreSQL Product and System Requirements
Reader/Writer | FME Form | FME Flow | FME Flow Hosted | Windows 64-bit | Linux | Mac |
Reader | Yes | Yes | Yes | Yes | Yes | Yes |
Writer | Yes | Yes | Yes | Yes | Yes | Ye |
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.
Reading Process
The reader opens a connection to the database, queries metadata, and queries 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.
Tables and Columns
- Truncation: 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.
- Naming: Spaces and special characters are permissible. Table and column names are case-sensitive. Qualified table naming is supported in the form <schemaname>.<tablename>.
- Schema: 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.
- Encoding: UNICODE support has been added to support system encoding. Although you cannot 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.
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.