About Implicit Text Casting in Greenplum Database

A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 5.x documentation.

About Implicit Text Casting in Greenplum Database

Greenplum Database version 4.3.x is based on PostgreSQL version 8.2. Greenplum Database version 5.x is based on PostgreSQL version 8.3. PostgreSQL 8.3 removed automatic implicit casts between the text type and other data types. When you migrate from Greenplum Database version 4.3.x to version 5.x, this change in behavior may impact existing applications and queries.

A cast specifies how to perform a conversion between two data types. Greenplum Database versions 4.3.x and 5.x support three types of casts:

Assignment cast - Greenplum Database implicitly invokes a cast in assignment contexts when you create the cast between two data types using the CREATE CAST ... AS ASSIGNMENT statement.

Implicit cast - Greenplum Database invokes a cast implicitly in any assignment or expression context when you create the cast between two data types using the CREATE CAST ... AS IMPLICIT statement. Greenplum Database 4.3.x also automatically adds an implicit cast from and to text for any data type.

Explicit cast - Greenplum Database explicitly applies a cast when you create a cast between two data types using the CAST(x AS typename) or x::typename syntax.

What is different in Greenplum Database 5.x?

Greenplum Database 5.x does not automatically implicitly cast between text and other data types. Greenplum Database 5.x also treats certain automatic implicit casts differently than version 4.3.x, and in some cases does not handle them at all. Applications or queries that you wrote for Greenplum Database 4.3.x that rely on automatic implicit casting may fail on Greenplum Database version 5.x.

(The term implicit cast, when used in the remainder of this section, refers to implicit casts automatically applied by Greenplum Database.)

Greenplum Database 5.x has downgraded implicit casts in the to-text type direction; these casts are now treated as assignment casts. A cast from a data type to the text type will continue to work in Greenplum Database 5.x if used in assignment contexts.

Greenplum Database 5.x no longer automatically provides an implicit cast in the to-text type direction that can be used in expression contexts. Additionally, Greenplum Database 5.x no longer provides implicit casts in the from-text type direction. When such expressions or assignments are encountered, Greenplum Database 5.x returns an error and the following message:

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

To illustrate, suppose you create two tables:

CREATE TABLE foo (a INT);
CREATE TABLE bar (b TEXT);

The following examples demonstrate certain types of text comparison queries that will fail on Greenplum Database 5.x.

Note: This is not an exhaustive list of failure scenarios.

Queries that reference text type and non-text type columns in an expression. For example:

Queries that perform comparisons between a text type column and a non-quoted literal such as an integer, number, float, or oid. For example:

SELECT * FROM bar WHERE b = 123;
ERROR: operator does not exist: text = integer
LINE 1: select * from bar where a = 123;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Queries that perform comparisons between a date type column or literal and an integer-like column. (Greenplum Database internally converts date types to the text type.) For example:

SELECT * FROM foo WHERE a = '20130101'::DATE;
ERROR: operator does not exist: integer = date
LINE 1: select * from foo where a = '20130101'::date;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The only supported workaround for the implicit casting differences between Greenplum Database versions 4.3.x and 5.x is to analyze failing applications and queries and update the application or query to use explicit casts to fix the failures.

If rewriting the application or query is not feasible, you may choose to temporarily work around the change in behaviour introduced by the removal of automatic implicit casts in Greenplum Database 5.x. There are two well-known workarounds to this PostgreSQL issue:

The workaround to re-create the implicit casts is not recommended as it breaks concatenation functionality. With the create missing operators workaround, you create the operators and functions that implement the comparison expressions that are failing in your applications and queries.

Workaround: Manually Creating Missing Operators

Warning: Use this workaround only to aid migration to Greenplum Database 5.x for evaluation purposes. Do not use this workaround in a production environment.

When you create an operator, you identify the data types of the left operand and the right operand. You also identify the name of a function that Greenplum Database invokes to evaluate the operator expression between the specified data types. The operator function evaluates the expression by performing either to-text or from-text conversion using the INPUT/OUTPUT methods of the data types involved. By creating operators for each (text type, other data type) and (other data type, text type) combination, you effectively implement the casts that are missing in Greenplum Database 5.x.

To implement this workaround, complete the following tasks after you install Greenplum Database 5.x:

Identify and note the names of the Greenplum 5.x databases in which you want to create the missing operators. Consider applying this workaround to all databases in your Greenplum Database deployment.

Identify a schema in which to create the operators and functions. Use a schema other than pg_catalog to ensure that these objects are included in a pg_dump or gpbackup of the database. This procedure will use a schema named cast_fix for illustrative purposes.

Review the blog entry Problems and workaround recreating implicit casts using 8.3+. The blog discusses this temporary workaround to the casting issue, i.e. creating missing operators. It also references a SQL script that you can run to create a set of equality (=) operators and functions for several text and other data type comparisons.

Download the 8.3 operator workaround.sql script referenced on the blog page, noting the location to which the file was downloaded on your local system.

The 8.3 operator workaround.sql script creates the equality operators and functions. Open the script in the editor of your choice, and examine the contents. For example, using the vi editor:

vi 8.3 operator workaround.sql

Notice that the script creates the operators and functions in the pg_catalog schema.

Replace occurrences of pg_catalog in the script with the name of the schema that you identified in Step 2, and then save the file and exit the editor. (You will create this schema in an upcoming step if the schema does not already exist.) For example:

:s/pg_catalog/cast_fix/g
:wq

Analyze your failing queries, identifying the operators and from-type and to-type data type comparisons that are the source of the failures. Compare this list to the contents of the 8.3 operator workaround.sql script, and identify the minimum set of additional operators and left_type/right_type expression combinations that you must support.

For each operator and left_type/right_type combination that you identify in the previous step, add CREATE statements for the following objects to the 8.3 operator workaround.sql script:

Create the function that implements the left_type operator right_type comparison. For example, to create a function that implements the greater than (>) operator for text (left_type) to integer (right_type) comparison:

You must create another operator and function if you want the operator to work in reverse (i.e. using the example above, if you want a greater than operator for integer (left_type) to text (right_type) comparison.)

For each database that you identified in Step 1, add the missing operators. For example:

Connect to the database as an administrative user. For example:

$ psql -d database1 -U gpadmin

Create the schema if it does not already exist. For example:

CREATE SCHEMA cast_fix;

Run the script. For example, if you downloaded the file to the /tmp directory:

\i '/tmp/8.3 operator workaround.sql'

You must create the schema and run the script for every new database that you create in your Greenplum Database cluster.

Identify and note the names of the users/roles to which you want to provide this capability. Consider exposing this to all roles in your Greenplum Database deployment.

For each role that you identified in Step 10, add the schema to the role's search_path. For example:

SHOW search_path;
ALTER ROLE bill SET search_path TO existing_search_path, cast_fix;