PostgreSQL / PostGIS

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 with less functionalities.

This driver requires a connection to a Postgres database. If you want to prepare a SQL dump to inject it later into a Postgres database, you can instead use the PostgreSQL SQL Dump driver (GDAL/OGR >= 1.8.0)

You can find additional information on the driver in the Advanced OGR PostgreSQL driver Information page.

Connecting to a database

To connect to a Postgres datasource, use a connection string specifying the database name, with additional parameters as necessary
PG:dbname=databasename
or
PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'"
It's also possible to omit the database name and connect to a default database, with the same name as the user name.
Note: We use PQconnectdb() to make the connection, so any other options and defaults that would apply to it, apply to the name here (refer to the documentation of the PostgreSQL server. Here for PostgreSQL 8.4). The PG: prefix is used to mark the name as a postgres connection string.

Geometry columns

If the geometry_columns table exists (i.e. PostGIS is enabled for the accessed database), then all tables and named views listed in the geometry_columns table will be treated as OGR layers. Otherwise (PostGIS disabled for the accessed database), all regular user tables and named views will be treated as layers.

Starting with GDAL 1.7.0, the driver also supports the geography column type introduced in PostGIS 1.5.

Starting with GDAL 2.0, the driver also supports reading and writing the following non-linear geometry types :CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON, MULTICURVE and MULTISURFACE

SQL statements

The PostgreSQL driver passes SQL statements directly to PostgreSQL by default, 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 directly through to PostgreSQL. It's also possible to request the ogr Pg driver to handle SQL commands with the OGR SQL engine, by passing "OGRSQL" string to the ExecuteSQL() method, as the name of the SQL dialect.

The PostgreSQL driver in OGR supports the OGRDataSource::StartTransaction(), OGRDataSource::CommitTransaction() and OGRDataSource::RollbackTransaction() calls in the normal SQL sense.

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 appropriately mapped into PostgreSQL.

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

By default it is assumed that text being sent to Postgres is in the UTF-8 encoding. This is fine for plain ASCII, but can result in errors for extended characters (ASCII 155+, LATIN1, etc). While OGR provides no direct control over this, you can set the PGCLIENTENCODING environment variable to indicate the format being provided. For instance, if your text is LATIN1 you could set the environment variable to LATIN1 before using OGR and input would be assumed to be LATIN1 instead of UTF-8. An alternate way of setting the client encoding is to issue the following SQL command with ExecuteSQL() : "SET client_encoding TO encoding_name" where encoding_name is LATIN1, etc. Errors can be caught by enclosing this command with a CPLPushErrorHandler()/CPLPopErrorHandler() pair.

Dataset open options

(GDAL >= 2.0)

Dataset Creation Options

None

Layer Creation Options

Configuration Options

There are a variety of Configuration Options which help control the behavior of this driver.

Examples

FAQs

See Also