# PostgreSQL

This is an experimental driver for prototyping options for using PostgreSQL (and PostgreSQL extended with PostGIS) as a vector database.

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 postgress 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.

Currently there is no support for coordinate systems in the PostgreSQL driver.

## Caveats

- The type recognition logic is currently quite impoverished. Only the types "VARCHAR*", "INT*", and "FLOAT*" are recognised directly. All other types are treated as VARCHAR.

- 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!

- The driver is not performance tuned. All geometry is passed back and forth in escaped BYTEA text form, there is no spatial indexing, etc.

- Some features are not implemented properly. For instance, spatial filtering is broken.

- Special field names, and other mechanisms are likely to change in the near future. This driver is only suitable to use for experimentation.

- 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.

### See Also

- PostgreSQL Home Page

- PostGIS