Merging multiple bytea rows based on id

I'm working with a postgres database where I need to merge multiple rows into a row based on ID.

ID  | A  | B  | C  |
--------------------
1   | x  |    |    |
1   | x  | y  |    |
2   | x  |    | z  |
3   |    | y  |    |
3   |    |    | z  |

A, B and C are bytea columns.

I need to merge it as follows:

ID  | A  | B  | C  |
--------------------
1   | x  | y  |    |
2   | x  |    | z  |
3   |    | y  | z  |

The problem occurs when I do GROUP BY on ID, as I'm not able to find a appropriate aggregate function for bytea columns.

1 answer

  • answered 2018-01-11 19:45 Juan Carlos Oropeza

    You can always do it with sub queries

    WITH allID as ( 
        SELECT distinct ID
        FROM YourTable
    )
    SELECT 
       ID, 
      (SELECT A FROM yourTable yt where yt.ID = ai.ID ORDER BY A LIMIT 1) as A,
      (SELECT B FROM yourTable yt where yt.ID = ai.ID ORDER BY B LIMIT 1) as B,
      (SELECT C FROM yourTable yt where yt.ID = ai.ID ORDER BY C LIMIT 1) as C
    FROM allID as ai