0

A software interface is inserting an undesired string into various columns of a table in Postgresql.

How can we search all rows and all columns to look for a string and update with null value?

I have not made any SQL because I didn't know where to start.

If someone can help with SQL..

Raky
  • 625
  • 5
  • 19
  • 1
    You can use a function - [to search every column of every table for a particular value](https://stackoverflow.com/a/50024712/10457877) – Georgy Aug 18 '21 at 07:31

1 Answers1

1

You can run through all rows and see what needs to get removed:

update mytable
set col1 = nullif (col1, 'bad string')
  , col2 = nullif (col2, 'bad string')
  , col3 = nullif (col3, 'bad string') 
  , ...

And if you want to prevent the software from inserting the undesired string in the future, I suggest you write a before-insert trigger for this, that replaces the undesired string with null right away.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73