<html>
<head>
<title>PostgreSQL</title>
</head>
<body bgcolor="#ffffff">
<h1>PostgreSQL</h1>
This driver implements support for access to spatial tables in
PostgreSQL extended with the
<a href="http://www.postgis.org/">PostGIS</a> spatial data support.
Some support exists in the driver for use with PostgreSQL without PostGIS
but with less functionalities.<p>
<h2>Connecting to a database</h2>
To connect to a Postgres datasource, use a connection string specifying the database name,
with additional parameters as necessary<br>
<blockquote><pre>PG:dbname=databasename</pre><em> or</em><pre>PG:"dbname='databasename' server='addr' port='5432' user='x' passwd='y'"</pre></blockquote>
It's also possible to omit the database name and connect
to a <em>default</em> database, with the same name as the user name.
<b>Note</b>: We use PQconnectdb() 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.<p>
Starting with GDAL 1.6.0, the set of tables to be scanned can be overridden by specifying
<i>tables=[schema.]table[(geom_column_name)][,[schema2.]table2[(geom_column_name2)],...]</i> within the connection string.
If the parameter is found, the driver skips enumeration of the tables as
described in the next paragraph.<p>
<h2>Geometry columns</h2>
If the <i>geometry_columns</i> table exists (i.e. PostGIS is enabled for the accessed
database), then all tables and named views listed in the <i>geometry_columns</i> 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.<p>
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 <i>geometry_columns</i> 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.<p>
<h2>Layers</h2>
Starting with GDAL 1.6.0, even when PostGIS is enabled, if the user defines the environment variable
<pre>PG_LIST_ALL_TABLES=YES</pre> (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 <i>geometry_columns</i> table.<p>
In any case, all user tables can be queried explicitly with GetLayerByName()<p>
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 <b>geometry</b> 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.<p>
Starting with GDAL 1.6.0, tables inherited from spatial tables are supported.<p>
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. <p>
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.<p>
<h2>Named views</h2>
When PostGIS is enabled for the accessed database, named views are supported, provided that
there is an entry in the <i>geometry_columns</i> 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 :
<pre>"INSERT INTO geometry_columns VALUES ( '', 'public', 'name_of_my_view', 'name_of_geometry_column', 2, 4326, 'POINT');"</pre>
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.
<h2>SQL statements</h2>
<p>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 <a href="/ogr/ogr_sql.html">OGR SQL</a> engine, by passing <strong>"OGRSQL"</strong>
string to the ExecuteSQL() method, as the name of the SQL dialect.</p>
<p>The PostgreSQL driver in OGR supports the OGRDataSource::StartTrasaction(),
OGRDataSource::CommitTransaction() and OGRDataSource::RollbackTransaction()
calls in the normal SQL sense.<p>
<h2>Caveats</h2>
<ul>
<li> 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. <p>
<li> A sequence object called <tablename>_ogc_fid_seq is created for new
tables (layer)<p>
<li> 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.<p>
</ul>
<h2>Creation Issues</h2>
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.<P>
As mentioned above the type system is impoverished, and many OGR types
are not appropriately mapped into PostgreSQL.<p>
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. <p>
<h3>Dataset Creation Options</h3>
None<p>
<h3>Layer Creation Options</h3>
<ul>
<li>
<b>GEOM_TYPE</b>: 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. <p>
<li> <b>OVERWRITE</b>: This may be "YES" to force an existing layer of the
desired name to be destroyed before creating the requested layer.<p>
<li> <b>LAUNDER</b>: 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.<p>
<li> <b>PRECISION</b>: 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".<p>
<li> <b>DIM={2,3}</b>: 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.<p>
<li> <b>GEOMETRY_NAME</b>: Set name of geometry column in new table. If
omitted it defaults to wkb_geometry.<p>
<li> <b>SCHEMA</b>: 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.<p>
<li> <b>SPATIAL_INDEX</b>: (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).<p>
</ul>
<h3>Environment variables</h3>
<ul>
<li><b>PG_USE_COPY</b>: This may be "YES" for using COPY for inserting data to Postgresql.
COPY is less robust than INSERT, but significantly faster.</li><p>
<li><b>PGSQL_OGR_FID</b>: Set name of primary key instead of 'ogc_fid'.</li><p>
</ul>
<h3>Example</h3>
<ul>
<li>
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. <p>
<pre>
% ogr2ogr -f PostgreSQL PG:dbname=warmerda abc.shp
</pre>
<li>
<p>
This second example loads a political boundaries layer from VPF (via the
<a href="drv_ogdi.html">OGDI driver</a>), 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.<p>
<pre>
% ogr2ogr -f PostgreSQL PG:dbname=warmerda \
gltp:/vrf/usr4/mpp1/v0eur/vmaplv0/eurnasia \
-lco OVERWRITE=yes -nln polbndl_bnd 'polbndl@bnd(*)_line'
</pre>
</li>
<li>
<p>
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. <p>
<pre>
% ogr2ogr -f PostgreSQL PG:dbname=warmerda tiger_michigan \
-lco OVERWRITE=yes CompleteChain
% ogr2ogr -update -append -f PostgreSQL PG:dbname=warmerda tiger_ohio \
CompleteChain
</pre>
</li>
<li>
<p>
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.<p>
<pre>
ogrinfo -ro PG:dbname=warmerda -sql "SELECT pop_1994 from canada where province_name = 'Alberta'"
</pre>
</li>
<li>
<p>
This example shows using ogrinfo to list PostgreSQL/PostGIS layers on a different host.<p>
<pre>
ogrinfo -ro PG:'host=myserver.velocet.ca user=postgres dbname=warmerda'
</pre>
</li>
<li>
<p>
This example shows using ogrinfo to list only the layers specified by the <i>tables=</i> options.<p>
<pre>
ogrinfo -ro PG:'dbname=warmerda tables=table1,table2'
</pre>
</li>
<li>
<p>
This example shows using ogrinfo to query a table 'foo' with multiple geometry columns ('geom1' and 'geom2').<p>
<pre>
ogrinfo -ro -al PG:dbname=warmerda 'foo(geom2)'
</pre>
</li>
</ul>
<h3>See Also</h3>
<ul>
<li> <a href="http://www.postgresql.org/">PostgreSQL Home Page</a><p>
<li> <a href="http://www.postgis.org/">PostGIS</a><p>
<li> <a href="http://www.postgis.org/support/wiki/index.php?OGR%20Examples">PostGIS / OGR Wiki Examples Page</a><p>
</ul>
</body>
</html>
