TREAT ( geometry-expression AS subtype )

Depends on the data type requested.

The TREAT function can only be used on geometries.

If the dynamic type of the expression is not a subtype of the target data type, an error is returned. The CAST function can also be used to change the declared type of a geometry expression. However, the CAST function allows changes outside of the subtype hierarchy. For example, CAST can be used to convert a point to a multipoint. These types of conversions may change the dynamic type of an expression in unexpected ways, so TREAT is preferable when moving from a supertype to a subtype. The TREAT function also executes more efficiently than the CAST function.

• Using the TREAT expression for subtypes
• CAST function [Data type conversion]

Execute the following in Interactive SQL to create a table and load two values into it:

DROP TABLE IF EXISTS treatExample;
CREATE TABLE treatExample( pk INT PRIMARY KEY, geo ST_Geometry );
INSERT INTO treatExample VALUES(0, NEW ST_Point(3,4) );
INSERT INTO treatExample VALUES(1, NEW ST_MultiPoint( new ST_Point( 5, 6 ) ) );

The following query returns the error "Type 'ST_Geometry' has no method named 'ST_X' (near 'T.geo.ST_X()')".

SELECT TREAT( geo AS ST_Point ).ST_X() FROM treatExample WHERE pk = 0;

The following query succeeds:

SELECT TREAT( geo AS ST_Point ) FROM treatExample WHERE pk = 0;

The following query returns the error "Cannot treat value ''SRID=0;MultiPoint ((5 6))'' as type ST_Point. The dynamic type is ST_MultiPoint".

SELECT TREAT( geo AS ST_Point ) FROM treatExample WHERE pk = 1;

The following query succeeds:

SELECT CAST( geo AS ST_Point ) FROM treatExample WHERE pk = 1;