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

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

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.

Starting with GDAL 1.6.0, the set of tables to be scanned can be overridden by specifying tables=[schema.]table[(geom_column_name)][,[schema2.]table2[(geom_column_name2)],...] within the connection string. If the parameter is found, the driver skips enumeration of the tables as described in the next paragraph.

Starting with GDAL 1.7.0, it is possible to restrict the schemas that will be scanned while establishing the list of tables. This can be done by specifying schemas=schema_name[,schema_name2] within the connection string. This can also be a way of speeding up the connection to a PostgreSQL database if there are a lot of schemas. Note that if only one schema is listed, it will also be made automatically the active schema (and the schema name will not prefix the layer name). Otherwise, the active schema is still 'public', unless otherwise specified by the active_schema= option.

Starting with GDAL 1.7.0, the active schema ('public' being the default) can be overridden by specifying active_schema=schema_name within the connection string. The active schema is the schema where tables are created or looked for when their name is not explicitly prefixed by a schema name. Note that this does not restrict the tables that will be listed (see schemas= option above). When getting the list of tables, the name of the tables within that active schema will not be prefixed by the schema name. For example, if you have a table 'foo' within the public schema, and a table 'foo' within the 'bar_schema' schema, and that you specify active_schema=bar_schema, 2 layers will be listed : 'foo' (implicetly within 'bar_schema') and 'public.foo'.

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.6.0, the PostgreSQL driver supports accessing tables with multiple PostGIS geometry columns. For such a table, there will be as many layers reported as the number of geometry columns listed for that table in the geometry_columns table. For example, if a table 'foo' has 2 geometry columns 'bar' and 'baz', 2 layers will be reported : 'foo(bar)' and 'foo(baz)'. For backward compatibility, if a table has only one geometry column, the layer name is the table name. Also if a table 'foo' has several geometry columns, with one being called 'wkb_geometry', the layer corresponding to this geometry column will be simply reported as 'foo'. Be careful - the behaviour in creation, update or deletion of layers that are based on tables with multiple PostGIS geometry column is known to have (not well-defined) side-effects on the other layers as they are closely tied. Thus, that capability should currently be thought as mostly read-only.

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

Layers

Starting with GDAL 1.6.0, even when PostGIS is enabled, if the user defines the environment variable

PG_LIST_ALL_TABLES=YES

(and does not specify tables=), all regular user tables and named views will be treated as layers. However, tables with multiple geometry column will only be reported once in that mode. So this variable is mainly useful when PostGIS is enabled to find out tables with no spatial data, or views without an entry in geometry_columns table.

In any case, all user tables can be queried explicitly with GetLayerByName()

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.

Starting with GDAL 1.6.0, tables inherited from spatial tables are supported.

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 layer name may be of the form "schema.table". The schema must exist, and the user needs to have write permissions for the target and the public schema.

Starting with GDAL 1.7.0, if the user defines the environment variable

PG_SKIP_VIEWS=YES

(and does not specify tables=), only the regular user tables will be treated as layers. The default action is to include the views. This variable is particularly useful when you want to copy the data into another format while avoiding the redundant data from the views.

Named views

When PostGIS is enabled for the accessed database, named views are supported, provided that there is an entry in the geometry_columns tables. But, note that the AddGeometryColumn() SQL function doesn't accept adding an entry for a view (only for regular tables). So, that must usually be done by hand with a SQL statement like :

"INSERT INTO geometry_columns VALUES ( '', 'public', 'name_of_my_view', 'name_of_geometry_column', 2, 4326, 'POINT');"

Starting with GDAL 1.6.0, it is also possible to use named views without inserting a row in the geometry_columns table. For that, you need to explicitly specify the name of the view in the "tables=" option of the connection string. See above. The drawback is that OGR will not be able to report a valid SRS and figure out the right geometry type.

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::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, date, time, timestamp and datetime are handled as date types and all other types are just treated as strings.
A sequence object called _ogc_fid_seq is created for new tables (layer)
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 appropriately mapped into PostgreSQL.

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.

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 catched by enclosing this command with a CPLPushErrorHandler()/CPLPopErrorHandler() pair.

Dataset Creation Options

None

Layer Creation Options

GEOM_TYPE: The GEOM_TYPE layer creation option can be set to one of "geometry", "geography" (PostGIS >= 1.5), "BYTEA" or "OID" to force the type of geometry used for a table. For a PostGIS database, "geometry" is the default value.
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 "_". If "NO" exact names are preserved. The default value is "YES". If enabled the table (layer) name will also be laundered.
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".
DIM={2,3}: Control the dimension of the layer. Defaults to 3. Important to set to 2 for 2D layers with PostGIS 1.0+ as it has constraints on the geometry dimension during loading.
GEOMETRY_NAME: Set name of geometry column in new table. If omitted it defaults to wkb_geometry for GEOM_TYPE=geometry, or the_geog for GEOM_TYPE=geography.
SCHEMA: Set name of schema for new table. Using the same layer name in different schemas is supported, but not in the public schema and others. Note that using the -overwrite option of ogr2ogr and -lco SCHEMA= option at the same time will not work, as the ogr2ogr utility will not understand that the existing layer must be destroyed in the specified schema. Use the -nln option of ogr2ogr instead, or better the active_schema connection string. See below example.
SPATIAL_INDEX: (From GDAL 1.6.0) Set to ON by default. Creates a spatial index on the geometry column to speed up queries. Set to OFF to disable. (Has effect only when PostGIS is available).

Environment variables

PG_USE_COPY: This may be "YES" for using COPY for inserting data to Postgresql. COPY is less robust than INSERT, but significantly faster.

PGSQL_OGR_FID: Set name of primary key instead of 'ogc_fid'.

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.
```
ogrinfo -ro PG:dbname=warmerda -sql "SELECT pop_1994 from canada where province_name = 'Alberta'"
```
This example shows using ogrinfo to list PostgreSQL/PostGIS layers on a different host.
```
ogrinfo -ro PG:'host=myserver.velocet.ca user=postgres dbname=warmerda'
```
This example shows using ogrinfo to list only the layers specified by the tables= options. (Starting with GDAL 1.6.0)
```
ogrinfo -ro PG:'dbname=warmerda tables=table1,table2'
```
This example shows using ogrinfo to query a table 'foo' with multiple geometry columns ('geom1' and 'geom2'). (Starting with GDAL 1.6.0)
```
ogrinfo -ro -al PG:dbname=warmerda 'foo(geom2)'
```
This example show how to list only the layers inside the schema apt200810 and apt200812. The layer names will be prefixed by the name of the schema they belong to. (Starting with GDAL 1.7.0)
```
ogrinfo -ro PG:'dbname=warmerda schemas=apt200810,apt200812'
```
This example shows using ogrinfo to list only the layers inside the schema named apt200810. Note that the layer names will not be prefixed by apt200810 as only one schema is listed. (Starting with GDAL 1.7.0)
```
ogrinfo -ro PG:'dbname=warmerda schemas=apt200810'
```
This example shows how to convert a set of shapefiles inside the apt200810 directory into an existing Postgres schema apt200810. In that example, we could have use the schemas= option instead. (Starting with GDAL 1.7.0)
```
ogr2ogr -f PostgreSQL "PG:dbname=warmerda active_schema=apt200810" apt200810
```
This example shows how to convert all the tables inside the schema apt200810 as a set of shapefiles inside the apt200810 directory. Note that the layer names will not be prefixed by apt200810 as only one schema is listed (Starting with GDAL 1.7.0)
```
ogr2ogr apt200810 PG:'dbname=warmerda schemas=apt200810'
```
This example shows how to overwrite an existing table in an existing schema. Note the use of -nln to specify the qualified layer name.
```
ogr2ogr -overwrite -f PostgreSQL "PG:dbname=warmerda" mytable.shp mytable -nln myschema.mytable
```
Note that using -lco SCHEMA=mytable instead of -nln wouldn't have worked in that case (see #2821 for more details).
If you need to overwrite many tables located in a schema at once, the -nln option is not the more appropriate, so it might be more convenient to use the active_schema connection string (Starting with GDAL 1.7.0). The following example will overwrite, if necessary, all the PostgreSQL tables corresponding to a set of shapefiles inside the apt200810 directory :
```
ogr2ogr -overwrite -f PostgreSQL "PG:dbname=warmerda active_schema=apt200810" apt200810
```

FAQs

Why can't I see my tables? PostGIS is installed and I have data
You must have permissions on all tables you want to read and geometry_columns and spatial_ref_sys.
Misleading behavior may follow without an error message if you do not have permissions to these tables. Permission issues on geometry_columns and/or spatial_ref_sys tables can be generally confirmed if you can see the tables by setting the configuration option PG_LIST_ALL_TABLES to YES. (e.g. ogrinfo --config PG_LIST_ALL_TABLES YES PG:xxxxx)