hello ,
can anyone help me with PostgreSQL? im trying to update certain column but i always found this error “ERROR: invalid input syntax for type numeric: “””… hope someone can help me
below is my script:
UPDATE table
SET column = RPAD(CAST(CAST(SUBSTRING(REPLACE(“column”,’-‘,”),1,3)AS numeric) + CAST((array_to_string(ARRAY(SELECT chr((48 + round(random() * 9)) :: integer) FROM generate_series(1,3)), ”)) AS numeric) AS text),3,chr((48 + round(random() * 9)) :: integer))
||’-‘||RPAD(CAST(CAST(SUBSTRING(REPLACE(“column”,’-‘,”),5,2)AS numeric) + CAST((array_to_string(ARRAY(SELECT chr((48 + round(random() * 9)) :: integer) FROM generate_series(1,2)), ”)) AS numeric) AS text),2,chr((48 + round(random() * 9)) :: integer))
||’-‘||RPAD(CAST(CAST(SUBSTRING(REPLACE(“column”,’-‘,”),8,4)AS numeric) + CAST((array_to_string(ARRAY(SELECT chr((48 + round(random() * 9)):: integer) FROM generate_series(1,4)), ”)) AS numeric) AS text),4,chr((48 + round(random() * 9)) :: integer));