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'.
For backward compatibility, it is also possible to query a layer with GetLayerByName() with a name formatted like 'foo(bar)' where 'foo' is a table and 'bar' a geometry column.
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.
"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.
Snippet example in Python :
feat = ogr.Feature(lyr.GetLayerDefn()) for i in range(100): feat.SetFID(-1) # Reset FID to null value lyr.CreateFeature(feat) print('The feature has been assigned FID %d' % feat.GetFID())or :
for i in range(100): feat = ogr.Feature(lyr.GetLayerDefn()) lyr.CreateFeature(feat) print('The feature has been assigned FID %d' % feat.GetFID())OGR < 1.8.0 behaviour can be obtained by setting the configuration option OGR_PG_RETRIEVE_FID to FALSE.
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.mytableNote 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