1.. _vector.pgdump: 2 3PostgreSQL SQL Dump 4=================== 5 6.. shortname:: PGDump 7 8.. built_in_by_default:: 9 10This write-only driver implements support for generating a SQL dump file 11that can later be injected into a live PostgreSQL instance. It supports 12PostgreSQL extended with the `PostGIS <http://postgis.net/>`__ 13geometries. 14 15This driver is very similar to the PostGIS shp2pgsql utility. 16 17Most creation options are shared with the regular PostgreSQL driver. 18 19The PGDump driver supports creating tables with 20multiple PostGIS geometry columns (following :ref:`rfc-41`) 21 22Driver capabilities 23------------------- 24 25.. supports_create:: 26 27.. supports_georeferencing:: 28 29.. supports_virtualio:: 30 31Creation options 32---------------- 33 34Dataset Creation Options 35~~~~~~~~~~~~~~~~~~~~~~~~ 36 37- **LINEFORMAT**: By default files are created with the line 38 termination conventions of the local platform (CR/LF on win32 or LF 39 on all other systems). This may be overridden through use of the 40 LINEFORMAT layer creation option which may have a value of **CRLF** 41 (DOS format) or **LF** (Unix format). 42 43Layer Creation Options 44~~~~~~~~~~~~~~~~~~~~~~ 45 46- **GEOM_TYPE**: The GEOM_TYPE layer creation option can be set to one 47 of "geometry" or "geography" (PostGIS >= 1.5) to force the type of 48 geometry used for a table. "geometry" is the default value. 49- **LAUNDER**: This may be "YES" to force new fields created on this 50 layer to have their field names "laundered" into a form more 51 compatible with PostgreSQL. This converts to lower case and converts 52 some special characters like "-" and "#" to "_". If "NO" exact names 53 are preserved. The default value is "YES". If enabled the table 54 (layer) name will also be laundered. 55- **PRECISION**: This may be "YES" to force new fields created on this 56 layer to try and represent the width and precision information, if 57 available using NUMERIC(width,precision) or CHAR(width) types. If 58 "NO" then the types FLOAT8, INTEGER and VARCHAR will be used instead. 59 The default is "YES". 60- **DIM={2,3,XYM,XYZM}**: Control the dimension of the layer. Important 61 to set to 2 for 2D layers with PostGIS 1.0+ as it has constraints on 62 the geometry dimension during loading. 63- **GEOMETRY_NAME**: Set name of geometry column in new table. If 64 omitted it defaults to *wkb_geometry* for GEOM_TYPE=geometry, or 65 *the_geog* for GEOM_TYPE=geography. 66- **SCHEMA**: Set name of schema for new table. Using the same layer 67 name in different schemas is supported, but not in the public schema 68 and others. 69- **CREATE_SCHEMA**: To be used in combination with 70 SCHEMA. Set to ON by default so that the CREATE SCHEMA instruction is 71 emitted. Turn to OFF to prevent CREATE SCHEMA from being emitted. 72- **SPATIAL_INDEX**\ =NONE/GIST/SPGIST/BRIN (starting with GDAL 2.4) or 73 YES/NO for earlier versions and backward compatibility: Set to GIST 74 (GDAL >=2.4, or YES for earlier versions) by default. Creates a 75 spatial index (GiST) on the geometry column to speed up queries (Has 76 effect only when PostGIS is available). Set to NONE (GDAL >= 2.4, or 77 FALSE for earlier versions) to disable. BRIN is only available with 78 PostgreSQL >= 9.4 and PostGIS >= 2.3. SPGIST is only available with 79 PostgreSQL >= 11 and PostGIS >= 2.5 80- **TEMPORARY**: Set to OFF by default. Creates a temporary table 81 instead of a permanent one. 82- **UNLOGGED**: Set to OFF by default. Whether to 83 create the table as a unlogged one. Unlogged tables are only 84 supported since PostgreSQL 9.1, and GiST indexes used for spatial 85 indexing since PostgreSQL 9.3. 86- **WRITE_EWKT_GEOM**: Set to OFF by default. Turn to ON to write EWKT 87 geometries instead of HEX geometries. This option will have no effect 88 if PG_USE_COPY environment variable is to YES. 89- **CREATE_TABLE**: Set to ON by default so that tables are recreated 90 if necessary. Turn to OFF to disable this and use existing table 91 structure. 92- **DROP_TABLE**\ =ON/OFF/IF_EXISTS: Defaults to IF_EXISTS. Set to ON so that 93 tables are destroyed before being recreated. Set to OFF to prevent 94 DROP TABLE from being emitted. Set to IF_EXISTS 95 in order DROP TABLE IF EXISTS to be emitted (needs PostgreSQL >= 8.2) 96- **SRID**: Set the SRID of the geometry. Defaults to -1, unless a SRS 97 is associated with the layer. In the case, if the EPSG code is 98 mentioned, it will be used as the SRID. (Note: the spatial_ref_sys 99 table must be correctly populated with the specified SRID) 100- **NONE_AS_UNKNOWN**: Can be set to TRUE to force 101 non-spatial layers (wkbNone) to be created as spatial tables of type 102 GEOMETRY (wkbUnknown). 103 Defaults to NO, in which case a regular table is created and not 104 recorded in the PostGIS geometry_columns table. 105- **FID**: Name of the FID column to create. Defaults 106 to 'ogc_fid'. 107- **FID64**: This may be "TRUE" to create a FID column 108 that can support 64 bit identifiers. The default value is "FALSE". 109- **EXTRACT_SCHEMA_FROM_LAYER_NAME**: Can be set to 110 NO to avoid considering the dot character as the separator between 111 the schema and the table name. Defaults to YES. 112- **COLUMN_TYPES**: A list of strings of format 113 field_name=pg_field_type (separated by comma) that should be use when 114 CreateField() is invoked on them. This will override the default 115 choice that OGR would have made. This can for example be used to 116 create a column of type 117 `HSTORE <http://www.postgresql.org/docs/9.0/static/hstore.html>`__. 118- **POSTGIS_VERSION**: Defaults to 2.2 starting with GDAL 3.2 (1.5 previously) 119 Possible values: 1.5, 2.0 or 2.2. 120 PostGIS 2.0 encodes differently non-linear geometry types. 121 And 2.2 brings special handling for POINT EMPTY geometries. 122- **DESCRIPTION** (From GDAL 2.1) Description string to put in the 123 pg_description system table. The description can also be written with 124 SetMetadataItem("DESCRIPTION", description_string). Descriptions are 125 preserved by default by ogr2ogr, unless the -nomd option is used. 126 127Environment variables 128~~~~~~~~~~~~~~~~~~~~~ 129 130- **PG_USE_COPY**: This may be "YES" for using COPY for inserting data 131 to Postgresql. COPY is significantly faster than INSERT. 132 133VSI Virtual File System API support 134~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 135 136The driver supports rwriting to files managed by VSI Virtual File System 137API, which include "regular" files, as well as files in the /vsizip/, 138/vsigzip/ domains. 139 140Writing to /dev/stdout or /vsistdout/ is also supported. 141 142Example 143~~~~~~~ 144 145- Simple translation of a shapefile into PostgreSQL into a file 146 abc.sql. The table 'abc' will be created with the features from 147 abc.shp and attributes from abc.dbf. The SRID is specified. 148 PG_USE_COPY is set to YES to improve the performance. 149 150 :: 151 152 % ogr2ogr --config PG_USE_COPY YES -f PGDump abc.sql abc.shp -lco SRID=32631 153 154- Pipe the output of the PGDump driver into the psql utility. 155 156 :: 157 158 % ogr2ogr --config PG_USE_COPY YES -f PGDump /vsistdout/ abc.shp | psql -d my_dbname -f - 159 160See Also 161~~~~~~~~ 162 163- :ref:`OGR PostgreSQL driver Page <vector.pg>` 164- `PostgreSQL Home Page <http://www.postgresql.org/>`__ 165- `PostGIS <http://postgis.net/>`__ 166- `PostGIS / OGR Wiki Examples 167 Page <http://trac.osgeo.org/postgis/wiki/UsersWikiOGR>`__ 168