KNOWLEDGE BASE

Error "function regexp_replace (uuid, unknown, unknown, unknown) does not exist" When Running Postgres Flow that Uses "Trim Spaces" with UUID Field


Published: 31 May 2019
Last Modified Date: 06 Jun 2019

Issue

When running a flow in Tableau Prep on a PostgreSQL connection that has the "Trim Spaces" cleaning step applied to UUID (Universal Unique Identiifier) fields, the flow fails and the following error might occur:
 

"Prep failure Error: function regexp_replace (uuid, unknown, unknown, unknown) does not exist; Error while executing the query" 

Environment

  • Tableau Prep 2019.1.4 and 2018.3
  • PostgreSQL connection

Resolution

Try one of the following workarounds:
  1. Remove any "Trim Spaces" cleaning steps from UUID fields in your PostgreSQL flow in Tableau Prep.
  2. If it is necessary to trim spaces in UUID fields, create a custom calculated field as a step in Tableau Prep instead, using the TRIM() function.
For more information about trimming, see the following Tableau Help documentation: Get Started with Tableau Prep Builder and Clean and Shape Data.

Cause

Starting with Tableau Prep 2018.3, the "Trim Spaces" implementation started using REGEXP_REPLACE(), whereas previously it was BTRIM(CAST [field] AS TEXT).

Tableau sending the REGEXP_REPLACE() function to Postgres invokes its own regexp_replace command, which is incompatible with the UUID field. The function REGEXP_REPLACE() does not convert UUID to text before running the REGEXP_REPLACE() query. 

The Tableau Prep team is aware of this issue (ID: 921755).
Did this article resolve the issue?