I have following sample data in my PostgreSQL 11 database:


I would like to divide/split above data into two parts, the one with unique ids, and the other with repeated ids.

My expected output is:

id	val1	val2
1   	70.4    40.6
2   	68.4    35.4
3   	34.6    79.5
5   	129.7   71.7

id  val1  val2
4   0     5
4   67    0
4   0     72.7
4   15.2  0
6   24.3  0
6   0     176.1

Can someone suggest me how this can be achieved?
Top Answer
Erwin Brandstetter
You can use [`EXISTS`]( to get all in a single query:


`ctid` is my fallback since you didn't declare a `PRIMARY KEY` (or any `UNIQUE` condition). There is another question discussing its nature:

@@@ answer 112

I added the column `repeated` to mark repeated rows. But you don't have to. The `EXISTS` expression can just go into the `ORDER BY` clause:


And I appended `val1` and `val2` to `ORDER BY` to get a stable sort order in the result. Also optional.
Answer #2
Jack Douglas
There are lots of ways to do this. Here is a solution using the windowing function version of `count`:


Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.