PostgreSQL data types supported in ArcGIS

When you create a table or add a column to a table in a database, you define a specific data type for the column. Data types determine the following:

• What values you can store in the column
• What operations you can use on the data in that column
• How the data in that column is stored in the database

ArcGIS works with specific data types. When you access a database table through a Database Connection, query layer, or web service, ArcGIS filters out any unsupported data types. ArcGIS does not display unsupported data types, and you cannot edit unsupported data types through ArcGIS. Similarly, when you use ArcGIS to copy and paste tables containing unsupported data types from one database to another, ArcGIS only pastes columns that use a supported data type.

The first column in the following table lists the ArcGIS data types. The second column lists the PostgreSQL data type that ArcGIS creates. The third column shows what other PostgreSQL data types (if any) map to the ArcGIS data type when you view a table that you created outside ArcGIS. The last column provides additional information when needed.

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,s)	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,s)		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, Geometry (PostGIS), Geography (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 (not a geodatabase), you must install it. See Add the ST_Geometry type to a PostgreSQL database for information. To use the PostGIS geometry or geography 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)	UUID
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 can exist per table.
Raster	bytea		Rasters are supported only in geodatabases.
Short Integer	smallint	real
Text	character varying	character, varchar, text

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 three spatial data types in PostgreSQL: Esri ST_Geometry, PostGIS geometry, and PostGIS geography. The next two sections provide more background on these 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.

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:

ST_Geometry subtypes

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.

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 spatial data types

PostGIS is a product that spatially enables PostgreSQL databases. PostGIS follows the OGC Simple Features specification for an 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. To use them, you must install PostGIS to your PostgreSQL database cluster, and use the PostGIS template database to create the database in which you will store your geodatabase. Be sure to install a version of PostGIS supported by the ArcGIS release you want to use.

When you use a PostGIS spatial storage type with ArcGIS, keep the following 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 or geography 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 this PostGIS-enabled database.

• The sde user and any user who accesses PostGIS data in the geodatabase or database must be granted permissions on specific PostGIS views.
• Feature classes that you create can only use the spatial references listed in the PostGIS public.spatial_ref_sys view. If you specify one that is not there, feature class creation fails.
• You must specify a configuration keyword that contains the GEOMETRY_STORAGE parameter set to PG_GEOMETRY (for PostGIS geometry) or PG_GEOGRAPHY (for PostGIS geography) to create a feature class that uses either of these spatial data types.
• Feature classes in a geodatabase in PostgreSQL that use the PostGIS types contain a field for CAD and curve storage, GDB_GEOMATTR_DATA. If you create a spatial table outside ArcGIS and register the table with the geodatabase, this field is added to your table.
• The PostGIS geography type limits the size of shapes it can process. See the PostGIS documentation for details.

Grant privileges to create PostGIS geometry or geography columns

When you enable a database for PostGIS, it adds three views to the public schema: geometry_columns, geography_columns, and spatial_ref_sys. You must grant SELECT privileges on the geometry_columns, geography_columns, spatial_ref_sys views to all users in the geodatabase, including the sde user.

GRANT select 
 ON public.geometry_columns 
 TO <login_name>;

GRANT select 
 ON public.geography_columns 
 TO <login_name>;

GRANT select
 ON public.spatial_ref_sys
 TO <login_name>;

Create feature classes that use a PostGIS spatial data type

ArcGIS uses a configuration parameter setting 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, PG_GEOMETRY (the setting for the PostGIS geometry type), or PG_GEOGRAPHY (the setting for the PostGIS geography type). Therefore, you must specify the configuration keyword that contains the GEOMETRY_STORAGE parameter set to the spatial data type you require: either PG_GEOMETRY or PG_GEOGRAPHY.

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. Or, if you want to store some of your feature classes in a PostGIS storage type, you can specify the PG_GEOMETRY or PG_GEOGRAPHY configuration keyword when you create your feature class. When exported from the geodatabase, these keywords appears as follows:

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

##PG_GEOGRAPHY
GEOMETRY_STORAGE    "PG_GEOGRAPHY"
UI_TEXT             "User Interface text description for POSTGIS geography storage" 
END

The rest of the storage parameters are obtained from the DEFAULTS keyword. For more information on configuration parameters, see PostgreSQL configuration parameters.

Use existing geometry or geography tables

ArcGIS can use tables containing PostGIS geometry or geography 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 spatial column. If it does not, define a query layer or view that includes only one of the spatial columns.
• The tables must contain no other columns of a user-defined type.
• Tables must have a single type of feature (points, lines, or polygons), though the feature type can be multipart.
• Each table must have an integer, unique, not-NULL column suitable as an Object ID column.
• Each table should have a spatial index.

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

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