CODEWITHSUNDEEP
sqlpostgresql
Ramesh
Ramesh

Reputation: 311

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) 
;

Upvotes: 4

Views: 997

Answers (4)

Abelisto
Abelisto

Reputation: 15624

Just as info, here is several approaches how to represet 16-bytes data without data loss:

with t(x) as (values(md5('abc123')))
select
  t.x, -- varchar(32), char(32)
  t.x::uuid, -- uuid
  ('x'||t.x)::bit(128), -- exactly bit(128)
  ('\x'||t.x)::bytea, -- bytea
  array[('x'||left(t.x,16))::bit(64)::bigint, ('x'||right(t.x,16))::bit(64)::bigint] -- bigint[]
  -- ... and so on
from t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Postgres has MD5 as a built-in function:

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

For the conversion back to bigint, give credit where credit is due. Erwin Brandstetter's answers are usually very thorough so I would expect it to work well.

Upvotes: 0

Nelson Teixeira
Nelson Teixeira

Reputation: 6610

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.

Upvotes: 1

user330315
user330315

Reputation:

I assume that this hashbyte() thing generates a hash value.

To create a md5 checksum in Postgres you can use the md5() function, e.g.:

md5(concat(id::text, name, created_date::text, last_modified_date::date))

concat() will take care of null values automatically, no need for coalesce()

Unfortunately there is no direct conversion from a hex value to an integer in Postgres

Upvotes: 1

Related Questions