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

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?

CodePudding user response：

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';