Update PostgreSQL JSON using system.data.dataset

Question

I'm trying to update a JSON field in PostgreSQL (v14) using PowerShell system.data.dataset method but keep getting error:

"ERROR [42883] ERROR: operator does not exist: json = unknown;

I just want to read the JSON field as a string then update a part of it (I don't care of identifying JSON fields I just want to treat it and update it as a string).

$DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$Server;Port=$Port;Database=$DB;Uid=$Uid;Pwd=$Pass;"
$Con = New-Object System.Data.Odbc.OdbcConnection;
$Con.ConnectionString = $DBConnectionString;
$Con.Open();

$Query  = "Select ID, INFO from TestTable"

$ODBCDataAdapter = New-Object system.Data.odbc.odbcDataAdapter
$DataSet = New-Object System.Data.DataSet
$ODBCCommandSelect = New-Object System.Data.Odbc.OdbcCommand($query,$Con)
$ODBCDataAdapter.SelectCommand = $ODBCCommandSelect  #execute Query 

#setup the command to execute to fill the in-memory table
$ODBCDataAdapter.SelectCommand = $ODBCCommandSelect  #execute Query 
$ODBCDataAdapter.Fill($dataSet)                      #in memory database table created

$Con.Close();
#I now have a $dataset containing the rows from the table

For($i=0; $i -le ($dataset.tables[0].rows.Count -1); $i++){
    IF($dataset.Tables[0].rows[$i].Info -match "Josh"){
        $dataSet.Tables[0].Rows[$i].Info = $dataSet.Tables[0].Rows[$i].Info -replace "Josh", "Barbara"
}


$ODBCCommandUpdate = New-Object System.Data.Odbc.OdbcCommandBuilder($ODBCDataAdapter)
$ODBCDataAdapter.UpdateCommand = $ODBCCommandUpdate.GetUpdateCommand()

#all working fine until here:
$ODBCDataAdapter.Update($Dataset.tables[0])

$ODBCDataAdapter.Dispose()
$Dataset.Dispose()
$con.Close()
$con.Dispose()

This works for all the other fields (i.e. non JSON). I tried a select statement like this: $Query = "Select ID, CAST(INFO as Varchar) from TestTable" And although it didn't error out, it still didn't update the field. here's what the table looks like

ID INFO(JSON)
1  { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
2  { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
3  {"customer": "Josh William", "items": {"product": "Toy Car", "qty": 1}}
4  { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}

Help me Stackoverflowbi Wan Kenobi, you're my only hope.

Answer 1

Found the answer: I added a line that changes the way that CommandBuilder creates the update statement: $ODBCCommandUpdate.ConflictOption =3

$now the builder changes the Where clause in the update statement to only include the primary key.