Checksum MD5 to generate row number in postgresql

Question

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?

You can avoid the error by only using functions that are [documented in the manual](https://www.postgresql.org/docs/current/static/functions.html) — , Mar 04 '20 at 08:20
May I know what does `RANDOM::TEXT` do? Is there anyway to execute and see what `RANDOM::TEXT` looks like or `CLOCK_TIMESTAMP()::TEXT` — The Great, Mar 04 '20 at 08:23
`::` is a type cast. Look into the documentation for the other functions. There is no `checksum` function in PostgreSQL. — Laurenz Albe, Mar 04 '20 at 10:18

score 1 · Accepted Answer · answered Mar 04 '20 at 12:33

1

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.

answered Mar 04 '20 at 12:33

Gordon Linoff

1,242,037
58
646
786

Thanks for the response. Upvoted. But what about MD5. MD5 works in postgres though but it can't be used as sequence number. Right? It turns it into a string? – The Great Mar 06 '20 at 10:13
@TheGreat . . . You can use `MD5()` if you are looking for a repeatable sequence. But SQL Server requires the chains of functions, because it doesn't have a simple `random()` function that gets called for every row. – Gordon Linoff Mar 06 '20 at 11:25

Checksum MD5 to generate row number in postgresql

1 Answers1