Dataset Creation Options

Dataset creation options can be specified in command-line tools using the syntax-dsco<NAME>=<VALUE> or by providing the appropriate arguments toGDALCreate() (C) orDriver.Create (Python).The following dataset creation options are supported:

• LINEFORMAT=[CRLF​/​LF]:By default files are created with the linetermination conventions of the local platform (CR/LF on win32 or LFon all other systems). This may be overridden through use of theLINEFORMAT dataset creation option which may have a value ofCRLF(DOS format) orLF (Unix format).

Layer Creation Options

Layer creation options can be specified in command-line tools using the syntax-lco<NAME>=<VALUE> or by providing the appropriate arguments toGDALDatasetCreateLayer() (C) orDataset.CreateLayer (Python).The following layer creation options are supported:

• GEOM_TYPE=[geometry​/​geography]: Defaults togeometry. TheGEOM_TYPE layer creation option can be set to oneof "geometry" or "geography" (PostGIS >= 1.5) to force the type ofgeometry used for a table.

• LAUNDER=[YES​/​NO]: Defaults toYES. This may be "YES" to force new fields created on thislayer to have their field names "laundered" into a form morecompatible with PostgreSQL. This converts to lower case and convertssome special characters like "-" and "#" to "_". If "NO" exact namesare preserved. If enabled the table (layer) name will also be laundered.

• LAUNDER_ASCII=[YES​/​NO]: (GDAL >= 3.9) Defaults toNO. Implies LAUNDER=YES, with the extra substitution of UTF-8 accentedcharacters in theLatin-1 SupplementandLatin Extented-Asets with the closest ASCII letter. Other non-ASCII characters arereplaced with underscore.Consequently this option is not appropriate for non-Latin languages.

• PRECISION=[YES​/​NO]: Defaults toYES. This may be "YES" to force new fields created on thislayer to try and represent the width and precision information, ifavailable 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​/​XYM​/​XYZM]:Control the dimension of the layer. Importantto set to 2 for 2D layers with PostGIS 1.0+ as it has constraints onthe geometry dimension during loading.

• GEOMETRY_NAME=value:Set name of geometry column in new table. Ifomitted it defaults towkb_geometry for GEOM_TYPE=geometry, orthe_geog for GEOM_TYPE=geography.

• SCHEMA=value:Set name of schema for new table. Using the same layername in different schemas is supported, but not in the public schemaand others.

• CREATE_SCHEMA=[ON​/​OFF]:To be used in combination withSCHEMA. Set to ON by default so that the CREATE SCHEMA instruction isemitted. Turn to OFF to prevent CREATE SCHEMA from being emitted.

• SPATIAL_INDEX=[NONE​/​GIST​/​SPGIST​/​BRIN]: Defaults toGIST. YES/NO for earlier versions and backward compatibility: Set to GIST(GDAL >=2.4, or YES for earlier versions) by default. Creates aspatial index (GiST) on the geometry column to speed up queries (Haseffect only when PostGIS is available). Set to NONE (GDAL >= 2.4, orFALSE for earlier versions) to disable. BRIN is only available withPostgreSQL >= 9.4 and PostGIS >= 2.3. SPGIST is only available withPostgreSQL >= 11 and PostGIS >= 2.5

• GEOM_COLUMN_POSITION=[IMMEDIATE​/​END]: (GDAL >= 3.7) Defaults toIMMEDIATE. If set to IMMEDIATE (the default), geometry columns are created in thetable structure as soon as OGR is instructed to create them (which typicallymeans that the default geometry column is created just after the FID columnand before the non-spatial columns). If set to END, the geometry columns arecreated in the table structure after non-spatial columns.

• TEMPORARY=[ON​/​OFF]: Defaults toOFF. Creates a temporary table instead of a permanent one.

• UNLOGGED=[ON​/​OFF]: Defaults toOFF. Whether tocreate the table as a unlogged one. Unlogged tables are onlysupported since PostgreSQL 9.1, and GiST indexes used for spatialindexing since PostgreSQL 9.3.

• WRITE_EWKT_GEOM=[ON​/​OFF]: Defaults toOFF. Turn to ON to write EWKTgeometries instead of HEX geometries. This option will have no effectPG_USE_COPY environment variable is to YES.

• CREATE_TABLE=[ON​/​OFF]: Defaults toON. Set to ON by default so that tables are recreatedif necessary. Turn to OFF to disable this and use existing tablestructure.

• DROP_TABLE=[ON​/​OFF​/​IF_EXISTS]: Defaults toIF_EXISTS. Set to ON so thattables are destroyed before being recreated. Set to OFF to preventDROP TABLE from being emitted. Set to IF_EXISTSin order DROP TABLE IF EXISTS to be emitted (needs PostgreSQL >= 8.2)

• SRID=value:Set the SRID of the geometry. Defaults to -1, unless a SRSis associated with the layer. In the case, if the EPSG code ismentioned, it will be used as the SRID. (Note: the spatial_ref_systable must be correctly populated with the specified SRID)

• NONE_AS_UNKNOWN=[YES​/​NO]:Can be set to YES to forcenon-spatial layers (wkbNone) to be created as spatial tables of typeGEOMETRY (wkbUnknown).Defaults to NO, in which case a regular table is created and notrecorded in the PostGIS geometry_columns table.

• FID=value: Defaults toogc_fid. Name of the FID column to create.Starting with GDAL 3.7, if set explicitly to the empty string,disables the creation of a FID column.

• FID64=[TRUE​/​FALSE]: Defaults toFALSE. This may be "TRUE" to create a FID columnthat can support 64 bit identifiers.

• EXTRACT_SCHEMA_FROM_LAYER_NAME=[YES​/​NO]: Defaults toYEs. Can be set toNO to avoid considering the dot character as the separator betweenthe schema and the table name.

• COLUMN_TYPES=value:A list of strings of formatfield_name=pg_field_type (separated by comma) that should be use whenCreateField() is invoked on them. This will override the defaultchoice that OGR would have made. This can for example be used tocreate a column of typeHSTORE.

• POSTGIS_VERSION=[1.5​/​2.0​/​2.2]:Defaults to 2.2 starting with GDAL 3.2 (1.5 previously)PostGIS 2.0 encodes differently non-linear geometry types.And 2.2 brings special handling for POINT EMPTY geometries.

• DESCRIPTION=value:Description string to put in thepg_description system table. The description can also be written withSetMetadataItem("DESCRIPTION", description_string). Descriptions arepreserved by default by ogr2ogr, unless the -nomd option is used.

Environment variables

Configuration options can be specified in command-line tools using the syntax--config<NAME>=<VALUE> or using functions such asCPLSetConfigOption() (C) orgdal.config_options (Python).The following configuration options are available:

• PG_USE_COPY=value:This may be "YES" for using COPY for inserting datato Postgresql. COPY is significantly faster than INSERT. COPY is used bydefault when inserting from a table that has just been created.

  Warning

  Warning

  At time of writing, PgPoolII is not compatible with COPYmode as used by the OGR PostgreSQL driver. Thus you shouldforce PG_USE_COPY=NO when using PgPoolII.

  Warning

  Warning

  It is not always possible to use COPY. In particular ifthe input layer has columns with a DEFAULT value specifiedand that the feature has no value set for that given field,the driver will default to INSERT even if instructed to useCOPY via this option.

VSI Virtual File System API support

The driver supports writing to files managed by VSI Virtual File SystemAPI, which include "regular" files, as well as files in the /vsizip/,/vsigzip/ domains.

Writing to /dev/stdout or /vsistdout/ is also supported.

Example

• Simple translation of a shapefile into PostgreSQL into a fileabc.sql. The table 'abc' will be created with the features fromabc.shp and attributes from abc.dbf. The SRID is specified.PG_USE_COPY is set to YES to improve the performance.

  ogr2ogr--configPG_USE_COPYYES-fPGDumpabc.sqlabc.shp-lcoSRID=32631

• Pipe the output of the PGDump driver into the psql utility.

  ogr2ogr--configPG_USE_COPYYES-fPGDump/vsistdout/abc.shp|psql-dmy_dbname-f-

See Also

• OGR PostgreSQL driver Page

• PostgreSQL Home Page

• PostGIS

• PostGIS / OGR Wiki ExamplesPage