Add a number for duplicate values in posgresql

I’ve a query in MySQL and I’m looking for a query which can perform below operation using Posgres

MySQL Query :

update APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3 a,
(
  SELECT @row_number:=CASE WHEN @email=email THEN @row_number+1 ELSE 1 END AS row_number,
    @email:=email AS email,id  
  FROM APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3, 
  (SELECT @row_number:=0,@email:='') AS t  
  ORDER BY email
) b 
set a.r_no=b.row_number where a.id=b.id

Output:

Email	Row Number
Aamir	1
Aamir	2
Aamir	3
Suresh	1
Suresh	2
Hafiz	1

Answer

WITH cte AS (
      SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_number
      FROM APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3
    )
UPDATE APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3
SET row_number = cte.row_number
FROM cte
WHERE APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3.id = cte.id

https://www.db-fiddle.com/f/3RqNHRGFjkU74v33upyfhi/0