CODEWITHSUNDEEP
sqlpostgresqlmd5checksum
The Great
The Great

Reputation: 7713

Checksum MD5 to generate row number in postgresql

I am trying to execute the below query which was found in my error report.

Error report is generated when we try to run a R package. This error report helps us know where is the error and how it can be reproduced. Upon investigation, I was able to see that it is below query which caused issue while R package execution.

select co.*, p.*,
      row_number() over (order by ABS(CHECKSUM(MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT))) % 123456789) rn
    from s1.depat co
    join s2.person p
      on co.subject_id = p.person_id

I understand row_number function is used to generate the row numbers but looks like Postgresql doesn't support all these functions like Checksum and MD5.

I get the below error

ERROR:  function checksum(text) does not exist
LINE 2:    row_number() over (order by ABS(CHECKSUM(MD5(RANDOM()::TE...
                                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 54

How can I avoid the above error and make this work in postgresql?

Upvotes: 0

Views: 1182

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

In Postgres, you would use random():

select co.*, p.*,
       row_number() over (order by random()) as rn
from s1.depat co join
     s2.person p
     on co.subject_id = p.person_id;

The use of checksum() suggests that the query generator thinks you are connected to SQL Server. There is probably a parameter somewhere to let it know to generate valid Postgres code.

Upvotes: 1

Related Questions