A comparison of ArcGIS, ArcSDE, and PostgreSQL data types

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 import data of one type into a column of another data type, you need to understand what the equivalent data types are between ArcSDE and your database management system (DBMS) because it can impact data content. Also, when creating new datasets in ArcGIS, it is helpful to know the equivalent data types between ArcGIS and your DBMS.

When you create a feature class or table in ArcGIS, there are 11 possible data types you can assign to a field. That means there are some data types that can only be created outside ArcGIS.

ArcSDE data type

PostgreSQL data type

ArcGIS data type

Notes

SE_STRING_TYPE

VARCHAR

TEXT

SE_INT16_TYPE (SE_SMALLINT_TYPE)

SMALLINT

SHORT INTEGER

SE_INT32_TYPE (SE_INTEGER_TYPE)

INTEGER

LONG INTEGER

SE_INT64_TYPE

BIGINT

na

The server configuration parameter INT64TYPES must be TRUE to create columns with this data type.

SE_FLOAT32_TYPE (SE_FLOAT_TYPE)

REAL

na

SE_FLOAT64_TYPE (SE_DOUBLE_TYPE)

NUMERIC(n,m)

na

SE_DATE_TYPE

TIMESTAMP WITHOUT ZONE

DATE

SE_UUID_TYPE

VARCHAR(38)

GUID

SE_BLOB_TYPE

BYTEA

BLOB

SE_SHAPE_TYPE

ST_GEOMETRY or GEOMETRY *

GEOMETRY

PostgreSQL data type depends on the configuration keyword used when creating the layer: ST_GEOMETRY or PG_GEOMETRY (PostGIS geometry).

SE_XML_TYPE

SDE_XML or XML

na

XML columns are not exposed in ArcGIS Desktop.

SE_RASTER_TYPE

BYTEA or ST_RASTER

RASTER

PostgreSQL data type depends on the raster storage specified in the DBTUNE table.

You must separately configure ST_Raster in the database. See Installing the ST_Raster type in PostgreSQL for instructions.

SE_STRING_TYPE

INTEGER

OBJECTID

NUMERIC(38,8)

FLOAT

NUMERIC(38,8)

DOUBLE

PostgreSQL data type comparison

*ST_Geometry and geometry are superclasses. When creating feature classes in ArcGIS Desktop, point feature classes created as ST_Geometry use the ST_Point subtype and point feature classes created as PostGIS geometry use the Point subtype for storage. For all other types of feature classes created in ArcGIS Desktop, the superclass is used. If you create spatial columns using SQL, the actual data subtype created depends on what type of subtype you specify.


8/19/2013