Why am I getting a function error when using replace() to replace strings in a table?

Question

I have a table in postgresql called 'raw_data_file' and it has a 7 columns and the last one is called 'status' and consists of strings only.

I am trying to replace all instances of 'PASS1_SUCCEEDED' with 'S3UPLOAD_SUCCEEDED'.

Here is my code:

select status from raw_data_file;

UPDATE raw_data_file 
  SET status = replace (status, 'PASS1_SUCCEEDED`', 'S3UPLOAD_SUCCEEDED');

For some reason, I am getting the error:

Error occurred during SQL query execution

Reason:
SQL Error [42883]: ERROR: function replace(raw_data_file_status_e, unknown, unknown) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 35
Error occurred during SQL query execution

Reason:
SQL Error [42883]: ERROR: function replace(raw_data_file_status_e, unknown, unknown) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 35

Any ideas what is causing this and how I can fix it?

What data type exactly is the status column? It seems that status is an enum, not a text (or varchar) column. — user330315
– user330315, Commented Aug 9, 2022 at 10:04

rouen · Accepted Answer · 2022-08-09 09:00:50Z

1

As error hints, try explicit casts:

UPDATE raw_data_file SET status = replace (status, 'PASS1_SUCCEEDED'::text, 'S3UPLOAD_SUCCEEDED'::text);

Btw, if your status field always contains only ONE of these text (so it cant contains things like PASS1_SUCCEEDED;PASS2_SUCCEEDED, you dont need string replace, you can simply do

UPDATE raw_data_file SET status = 'S3UPLOAD_SUCCEEDED' WHERE status = 'PASS1_SUCCEEDED';

answered Aug 9, 2022 at 9:00

rouen

5,1742 gold badges28 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick_Chong Over a year ago

Awsome thanks- your second point has saved me a lot of time :)

combinatorist Over a year ago

For me, explicit types helped with this error because it lead to a new error where I hadn't noticed my deeply nested functions were missing a comma. (Specifically, a comma after the closing parens of an inner replace function and before the outer replace function's other arguments).

Collectives™ on Stack Overflow

Why am I getting a function error when using replace() to replace strings in a table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related