I need to insert hash value into column b based on value of column a, but I need to do this for every row in table.
I always get this error no matter what I tried:
ERROR: more than one row returned by a subquery used as an expression
I have been trying different versions of the following:
UPDATE table
SET column b = md5((SELECT column a FROM table))
WHERE column a IS NOT NULL;
Any suggestions on how to perform this operation?
No need for a subquery here. As I understand, you want to store the checksum of column_a
in column_b
. As one would expect, Postgres' md5()
function expects a single, scalar argument of string datatype, so:
UPDATE table
SET column_b = md5(column_a)
WHERE column_a IS NOT NULL;
Note that it would probably be simpler to use a computed column (available in Postgres 12) to store this derived information.