PostgreSQL

This driver implements support for access to spatial tables in PostgreSQL extended with the PostGIS spatial data support. Some support exists in the driver for use with PostgreSQL without PostGIS but this has not been recently tested and is likely decayed.

When opening a database, it's name should be specified in the form "PG:dbname=". We use PQconnectcb() to make the connection, so any other options and defaults that would apply to it, apply to the name here. The PG: prefix is used to mark the name as a postgres connection string.

Currently all regular user tables are assumed to be layers from an OGR point of view, with the table names as the layer names. Named views are not currently support.

Regular (non-spatial) tables can be accessed, and will return features with attributes, but not geometry. If the table has a "wkb_geometry" field, it will be treated as a spatial table. The type of the field is inspected to determine how to read it. It can be a PostGIS geometry field, which is assumed to come back in OGC WKT, or type BYTEA or OID in which case it is used as a source of OGC WKB geometry.

If there is an "ogc_fid" field, it will be used to set the feature id of the features, and not treated as a regular field.

The PostgreSQL driver passes SQL statements directly to PostgreSQL rather than evaluating them internally when using the ExecuteSQL() call on the OGRDataSource, or the -sql command option to ogr2ogr. Attribute query expressions are also passed through to PostgreSQL. The PostgreSQL driver in OGR supports the OGRDataSource::StartTrasaction(), OGRDataSource::CommitTransaction() and OGRDataSource::RollbackTransaction() calls in the normal SQL sense.

Caveats

The type recognition logic is currently somewhat impoverished. The types "INT*" and "NUMERIC(width,0)" are mapped to integer, the types "FLOAT*" and "NUMERIC(width,precision>0)" are mapped to real, and all other types are just treated as strings.
BYTEA fields in PostgreSQL version preceeding 7.1 don't support more than 8K in a record, so significant sized geometries on a feature will trigger an errow about the INSERT command being to big for the buffer. Upgrade to 7.1.x!
Some features are not implemented properly. For instance, spatial filtering is broken.
A sequence object called _ogc_fid_seq is created for new tables (layer), and must be dropped separately from the table when cleaning up.
Sequential reading is done within a single transaction. Any attempts to write to a layer within a sequential read will likely result in a "BEGIN" while already within a transaction type of error message.

Creation Issues

The PostgreSQL driver does not support creation of new datasets (a database within PostgreSQL), but it does allow creation of new layers within an existing database.

As mentioned above the type system is impoverished, and many OGR types are not appropriate mapped into PostgreSQL.

Currently only sequential writing is supported.

If the database has PostGIS types loaded (ie. the geometry type) newly created layers will be created with the PostGIS Geometry type. Otherwise they will use OID.

Dataset Creation Options

None

Layer Creation Options

GEOM_TYPE: The GEOM_TYPE layer creation option can be set to one of "Geometry", "BYTEA" or "OID" to force the type of geometry used for a table.
OVERWRITE: This may be "YES" to force an existing layer of the desired name to be destroyed before creating the requested layer.
LAUNDER: This may be "YES" to force new fields created on this layer to have their field names "laundered" into a form more compatible with PostgreSQL. This converts to lower case and converts some special characters like "-" and "#" to "_". The default value is "NO".
PRECISION: This may be "YES" to force new fields created on this layer to try and represent the width and precision information, if available using NUMERIC(width,precision) or CHAR(width) types. If "NO" then the types FLOAT8, INTEGER and VARCHAR will be used instead. The default is "YES".

Example

Simple translation of a shapefile into PostgreSQL. The table 'abc' will be created with the features from abc.shp and attributes from abc.dbf. The database instance (warmerda) must already exist, and the table abc must not already exist.

% ogr2ogr -f PostgreSQL PG:dbname=warmerda abc.shp

This second example loads a political boundaries layer from VPF (via the OGDI driver), and renames the layer from the cryptic OGDI layer name to something more sensible. If an existing table of the desired name exists it is overwritten.

% ogr2ogr -f PostgreSQL PG:dbname=warmerda \
        gltp:/vrf/usr4/mpp1/v0eur/vmaplv0/eurnasia \
        -lco OVERWRITE=yes -nln polbndl_bnd 'polbndl@bnd(*)_line'

In this example we merge tiger line data from two different directories of tiger files into one table. Note that the second invocation uses -append and no OVERWRITE=yes.

% ogr2ogr -f PostgreSQL PG:dbname=warmerda tiger_michigan \
     -lco OVERWRITE=yes CompleteChain
% ogr2ogr -update -append -f PostgreSQL PG:dbname=warmerda tiger_ohio \
     CompleteChain

This example shows using ogrinfo to evaluate an SQL query statement within PostgreSQL. More sophisticated PostGIS specific queries may also be used via the -sql commandline switch to ogrinfo.