PostgreSQL data types supported in ArcGIS

When you create a table or add a column to a table in the database, columns are created as a specific data type. Data types are classifications that identify possible values for and operations that can be done on the data, as well as the way the data in that column is stored in the database.

When you access database tables from ArcGIS, you can work with specific data types. When accessing the database table through the Database Connections node in the Catalog tree or through a query layer in ArcMap, ArcGIS filters out any unsupported data types. If you access your database table directly, unsupported data types won't be displayed in the ArcGIS interface and you cannot edit them through ArcGIS. Similarly, when you copy tables containing unsupported data types with ArcGIS, it will only copy the supported data types; when you paste the table to another database or a geodatabase, the unsupported data type columns will not be present.

When you create a feature class or table in ArcGIS or add a column to an existing table or feature class using ArcGIS, there are 12 possible ArcGIS data types that can be assigned to a field. Database data types that don't correspond to these types cannot be used directly in ArcGIS client applications.

The first column lists the ArcGIS data types. The second column lists the PostgreSQL data type that will be created in the database. The third column shows what other PostgreSQL data types (if any) map to the ArcGIS data type when viewed in ArcGIS.

ArcGIS data types	PostgreSQL data types created	Other PostgreSQL data types that can be viewed	Notes
BLOB	BYTEA
DATE	TIMESTAMP WITHOUT ZONE	TIMESTAMP
DOUBLE	NUMERIC(p)	BIG SERIAL, DOUBLE PRECISION	The precision and scale specified in ArcGIS can affect the resultant data type created in the database. See ArcGIS field data types for more information.
FLOAT	NUMERIC(p)		The precision and scale specified in ArcGIS can affect the resultant data type created in the database. See ArcGIS field data types for more information.
GEOMETRY	ST_GEOMETRY or GEOMETRY (PostGIS)		In a geodatabase, the GEOMETRY_STORAGE setting of the configuration keyword used when creating the feature class determines which data type is created in the database. To use ST_Geometry in a database, you must install it. See Add the ST_Geometry type to a PostgreSQL database for information. To use the PostGIS geometry type, you must install PostGIS in your PostgreSQL database cluster, and the database itself must be enabled to use PostGIS. See your PostgreSQL documentation for more information.
GLOBAL ID	VARCHAR(38)		Only supported in geodatabases.
GUID	VARCHAR(38)
LONG INTEGER	INTEGER	SERIAL
OBJECT ID	INTEGER in a geodatabase SERIAL in a database		The ArcGIS type ObjectID is the registered row ID column for the table (or feature class). Only one may exist per table.
RASTER	BYTEA or ST_RASTER		Rasters are supported only in geodatabases. Which PostgreSQL data type is created depends on the configuration keyword used when creating the raster catalog, raster dataset, or mosaic dataset. You must separately configure ST_Raster in the geodatabase if you want to use it. See Install ST_Raster in PostgreSQL for instructions.
SHORT INTEGER	SMALLINT	REAL
TEXT	CHARACTER VARYING	CHARACTER, VARCHAR, TEXT

PostgreSQL data types supported in ArcGIS

If your table contains a column with a data type not supported in ArcGIS, you can cast the column to text. However, only do this if you just want to see the values in the column; do not do this if you need to perform any analysis that uses the values in that column. For example, you could execute a SELECT statement to choose the columns in tableb and cast the decimal column (total) to text:

SELECT id, name, total::text
FROM me.mydb.tableb;

Geometry data types

As indicated in the table, ArcGIS creates and can work with two geometry data types in PostgreSQL: Esri ST_Geometry or PostGIS Geometry. The next two sections provide more background on these two data types.

ST_Geometry

The following is a general description of the ST_Geometry spatial data type. For information specific to the PostgreSQL implementation, see ST_Geometry in PostgreSQL.

The ST_Geometry data type implements the SQL 3 specification of user-defined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. It provides International Organization for Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant structured query language (SQL) access to the geodatabase and database. This storage extends the capabilities of the database by providing storage for objects (points, lines, and polygons) that represent geographic features. It was designed to make efficient use of database resources, to be compatible with database features such as replication and partitioning, and to provide rapid access to spatial data.

ST_Geometry itself is an abstract, noninstantiated superclass. However, its subclasses can be instantiated. An instantiated data type is one that can be defined as a table column and have values of its type inserted into it.

Although you can define a column as type ST_Geometry, you do not insert ST_Geometry values into the column since it cannot be instantiated. Instead, you insert the subclass values.

The following chart demonstrates the hierarchy of the ST_Geometry data type and its subclasses.

The ST_Geometry superclass and its subclasses

ST_Geometry's subclasses are divided into two categories: the base geometry subclasses and the homogeneous collection subclasses. The base geometries include ST_Point, ST_LineString, and ST_Polygon, while the homogeneous collections include ST_MultiPoint, ST_MultiLineString, and ST_MultiPolygon. As the names imply, the homogeneous collections are collections of base geometries. In addition to sharing base geometry properties, homogeneous collections have some of their own properties.

Each subclass stores the type of geometry implied by its name; for instance, ST_MultiPoint stores multipoints. A list of the subclasses and their descriptions are in the following table:

Subtype	Description
ST_Point	A zero-dimensional geometry that occupies a single location in coordinate space. Has a single x,y coordinate value, is always simple, and has a NULL boundary.
ST_LineString	A one-dimensional object stored as a sequence of points defining a linear interpolated path. ST_LineStrings have length. The ST_LineString is simple if it does not intersect its interior. The endpoints (the boundary) of a closed ST_LineString occupy the same point in space. An ST_LineString is a ring if it is both closed and simple. The endpoints normally form the boundary of an ST_LineString unless the ST_LineString is closed, in which case the boundary is NULL. The interior of an ST_LineString is the connected path that lies between the endpoints, unless it is closed, in which case the interior is continuous.
ST_Polygon	A two-dimensional surface stored as a sequence of points defining its exterior bounding ring and zero or more interior rings. ST_Polygon has area and is always simple. The exterior and any interior rings define the boundary of an ST_Polygon, and the space enclosed between the rings defines the ST_Polygon's interior. The rings of an ST_Polygon can intersect at a tangent point but never cross.
ST_MultiPoint	A collection of ST_Points. Has a dimension of 0. An ST_MultiPoint is simple if none of its elements occupy the same coordinate space. The boundary of an ST_MultiPoint is NULL.
ST_MultiLineString	A collection of ST_LineStrings. ST_MultiLineStrings have length. ST_MultiLineStrings are simple if they only intersect at the endpoints of the ST_LineString elements. ST_MultiLineStrings are nonsimple if the interiors of the ST_LineString elements intersect. The boundary of an ST_MultiLineString is the nonintersected endpoints of the ST_LineString elements. The ST_MultiLineString is closed if all of its ST_LineString elements are closed. The boundary of an ST_MultiLineString is NULL if all the endpoints of all the elements are intersected.
ST_MultiPolygon	A collection of polygons. ST_MultiPolygons have area. The boundary of an ST_MultiPolygon is the cumulative length of its elements' exterior and interior rings. The interior of an ST_MultiPolygon is defined as the cumulative interiors of its element ST_Polygons. The boundary of an ST_MultiPolygon's elements can only intersect at a tangent point.

ST_Geometry subtypes

Note that each subclass inherits the properties of the ST_Geometry superclass but also has properties of its own. Functions that operate on the ST_Geometry data type accept any of the subclass entity types. However, some functions have been defined at the subclass level and only accept certain subclasses. For example, the ST_GeometryN function only takes ST_MultiLinestring, ST_MultiPoint, or ST_MultiPolygon subtype values as input.

PostGIS Geometry

PostGIS is a product that spatially enables PostgreSQL databases. PostGIS follows the Open Geospatial Consortium, Inc. (OGC), Simple Features specification for a Structured Query Language (SQL). It uses the OGC well-known binary (WKB) and well-known text (WKT) representations of geometry.

PostGIS has two spatial type options: geometry and geography. Only the geometry type is supported with ArcGIS.

When you use the PostGIS geometry storage type with ArcGIS, keep the following rules in mind:

• You must use the PostGIS database template to create the PostgreSQL database you use for your geodatabase or enable PostGIS in the database.

  Note:

  If you create your geodatabase using the Create Enterprise Geodatabase geoprocessing tool, your database is created using a template other than the PostGIS template. Therefore, if you want to use the PostGIS geometry type, you must create a database manually using the PostGIS template. Then you can specify your existing database when you run the Create Enterprise Geodatabase geoprocessing tool, and the geodatabase will be created in it.

• The sde user and any user who creates data in the geodatabase must be granted permissions on specific PostGIS tables.
• Feature classes that you create can only use the spatial references listed in the PostGIS public.spatial_ref_sys table. If you specify one that is not in that table, feature class creation fails.
• You must specify a configuration keyword that contains the GEOMETRY_STORAGE parameter set to PG_GEOMETRY to create a feature class that uses the PostGIS geometry type.
• You cannot rename spatial tables stored using the PostGIS geometry type. This is because there is no PostGIS function to update the table name in the public.geometry_columns table.

To use the PostGIS geometry type, you must install PostGIS. Install PostGIS after you install PostgreSQL. Be sure to install a version of PostGIS supported by the ArcGIS release you want to use.

When you install PostGIS, a PostGIS template database is created in the PostgreSQL database cluster. Use the PostGIS template database to create a database in which to store your geodatabase.

Grant privileges to create PostGIS geometry columns

When a database is enabled for PostGIS, two tables—geometry_columns and spatial_ref_sys—are created in the public schema. You must grant, at a minimum, SELECT, INSERT, UPDATE, and DELETE privileges on the geometry_columns table and SELECT on the spatial_ref_sys table to the sde user and any users who will create data in the geodatabase.

GRANT select, insert, update, delete 
ON TABLE public.geometry_columns 
TO <user_name>;

GRANT select
ON TABLE public.spatial_ref_sys
TO <user_name>;

Create feature classes that use PostGIS geometry

ArcGIS uses a configuration parameter setting in the sde_dbtune table to determine what spatial data type to use when you create a feature class. This parameter is GEOMETRY_STORAGE. In geodatabases in PostgreSQL, this can be set to either ST_GEOMETRY or PG_GEOMETRY (the setting for the PostGIS geometry type). Therefore, when you want to create a feature class using ArcGIS that uses the PostGIS geometry type, specify a configuration keyword that contains the GEOMETRY_STORAGE parameter set to PG_GEOMETRY.

By default, new feature classes use ST_Geometry storage. If you want to store most of your data in PostGIS storage types, alter the GEOMETRY_STORAGE parameter value under the DEFAULTS configuration keyword in the sde_dbtune table to PG_GEOMETRY. Or, if you want to store just some of your feature classes in the PostGIS storage type, you can specify the PG_GEOMETRY configuration keyword when you create your feature class. When exported from the geodatabase, the PG_GEOMETRY keyword appears as follows:

##PG_GEOMETRY
GEOMETRY_STORAGE    "PG_GEOMETRY"
UI_TEXT             "User Interface text description for POSTGIS geometry storage" 
END

The rest of the storage parameters are picked up from the DEFAULTS keyword. For more information on configuration keywords and parameters, see the following topics:

• Configuration keywords
• Configuration parameters in PostgreSQL

Use existing geometry tables

ArcGIS can use tables containing PostGIS geometry columns created externally by other applications or using SQL (also referred to as third-party tables) as long as the tables meet the following prerequisites:

• Each table must have a single geometry column. If it does not, define a query layer or view that includes only one of the geometry columns.
• The tables must contain no other columns of a user-defined type.
• Tables must have a single type of geometry (points, lines, or polygons), though geometry can be multipart.
• Each table must have an integer, unique, not-NULL column suitable as a registered row ID column.
• Each table should have a spatial index.

For information on creating tables with a PostGIS column using SQL, see the PostGIS documentation.

You can connect to a PostgreSQL database that contains PostGIS tables from ArcGIS for Desktop and register them with the geodatabase. See Register a table with the geodatabase for more information.

Raster data types

You can use either bytea or ST_Raster data types to store raster in a geodatabase in PostgreSQL. Bytea is used by default. If you want to use ST_Raster, you must configure the geodatabase to use it.

Bytea

Bytea is a variable-length binary string data type used by PostgreSQL. It is similar to a BLOB. See the PostgreSQL documentation for more information on this data type.

ST_Raster

ST_Raster is a user-defined data type you can install in enterprise geodatabases to provide SQL access to raster data.

To use the ST_Raster type, you must configure it in the database.

For in-depth information on how the ST_Raster object type is defined, see The ST_Raster data type.