Using PostgreSQL Case When with Partition

I am trying to use Case when with partition to create a new row and mark whether its duplicated or not

My desired output is shown below

The query I am using

I cant find the error here. It seemed to work in SQL Server before

SELECT *,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY 
            x , y > 1) THEN TRUE ELSE FALSE
    END AS is_duplicated
    FROM users
   

Answer

I think > 1 should be out of (PARTITIONBY x, y) like this:

SELECT *,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY 
            x , y) > 1 THEN TRUE ELSE FALSE
    END AS is_duplicated
    FROM users

Comprasion operator itself results on boolean, so you don’t need case:

SELECT *,
     
       ROW_NUMBER() OVER (PARTITION BY 
            x , y) > 1
     AS is_duplicated
    FROM users

But this will result, marking duplicate only the rows after the first row, meaning first Carlo Thomas will not be duplicate.

So for the desired result, you need this:

SELECT *,
     
       (SELECT count(*) from users t WHERE t.x=u.x and t.y=u.y) > 1
     AS is_duplicated
    FROM users u;