Ramesh Ramesh - 7 months ago 20
SQL Question

Sql to postgresql

I have a code in sql which I am using. Not much familiar with postgresql. Below is the code which I want to convert to postgresql. I'm using dbeaver 3.5.4

Update tablename
set record_hash = cast(hashbytes('MD5',
coalesce(id, '') +
coalesce(name, '') +
coalesce(created_date, '') +
coalesce(last_modified_date, '')
) as bigint)
;

Answer

You can do it like this:

Update tablename
set record_hash = ('x'|| substr(
                            md5(
                              coalesce(id, '') ||
                              coalesce(name, '') ||
                              coalesce(created_date, '') ||
                              coalesce(last_modified_date, '')
                            ),1,16)::bit(64)::bigint )

Found here how to do the hash => bigint conversion.

Comments