When opening a database, it's name should be specified in the form
"PG:dbname=
If the geometry_columns table exists, then all listed tables and
named views will be treated as OGR layers. Otherwise all regular user tables
will be treated as layers.
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 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.
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.
As mentioned above the type system is impoverished, and many OGR types
are not appropriate 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+ for instance). Whle OGR provides no direct
control over this, you can set the PGCLIENTENCODING environment variable
to indicate the format being provided. For instance, if you 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.
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.
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.
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.
This example shows using ogrinfo to list PostgreSQL/PostGIS layers on a different host.
Caveats
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.Dataset Creation Options
NoneLayer Creation Options
Environment variables
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
% ogr2ogr -f PostgreSQL PG:dbname=warmerda \
gltp:/vrf/usr4/mpp1/v0eur/vmaplv0/eurnasia \
-lco OVERWRITE=yes -nln polbndl_bnd 'polbndl@bnd(*)_line'
% ogr2ogr -f PostgreSQL PG:dbname=warmerda tiger_michigan \
-lco OVERWRITE=yes CompleteChain
% ogr2ogr -update -append -f PostgreSQL PG:dbname=warmerda tiger_ohio \
CompleteChain
ogrinfo -ro PG:dbname=warmerda -sql "SELECT pop_1994 from canada where province_name = 'Alberta'"
ogrinfo -ro PG:'host=myserver.velocet.ca user=postgres dbname=warmerda'
See Also