Search code examples
sqlpostgresqlunique

Getting only unique values in postresql

I need something like:

SELECT * FROM TABLE WHERE <value in column1 is always unique 
(if ever any value will be noticed more than once, then skip this row)>

in postgresql.

So if I have these rows in table:

1;"something";"xoxox"
2;"other";"xoxox"
3;"something";"blablabla"

And then go with the query, then that should be result:

2;"other";"xoxox"

Any ideas?

Solution

  • Use count(*) as a window function:

    select t.*
from (select t.*, count(*) over (partition by col1) as cnt
      from t
     ) t
where cnt = 1;

    Alternatively, you can use not exists and the id column:

    select t.*
from t
where not exists (select 1 from t t2 where t2.col1 = t.col1 and t2.id <> t.id);