Combining two rows from a table into one

Question

I have a table where I've noticed that the unique constraint was set incorrectly, and duplicate rows have entered the table.

I set up this sqlfiddle: http://sqlfiddle.com/#!15/c4a5d/1/0

create table foo (
bad_id INT NOT NULL,
real_id INT NOT NULL,
a TEXT,
b TEXT
);

insert into foo values
(1, 1, 'a1', null),
(2, 1, null, 'b1'),
(3, 1, null, null),
(4, 2, 'a22', 'b2'),
(5, 2, 'a2', 'b22'),
(6, 3, null, null);

I'm trying to fix the table by coalescing values. Where a newer row exists, I want to take those values (It should have been an update instead of insert)

The final result I'd like is this, unique on real_id

3 | 1 | 'a1' | 'b1'
5 | 2 | 'a2' | 'b22'
6 | 3 | null | null

Basically I want the end result to look as if the first row was an insert and any following rows with the same real_id were partial updates

What kind of query can I use to create that final result set?

I'm using Postgres 9.4.

If what's needed to do this in sql is terrible or has very bad asymptotic performance I should be able to do it with linear complexity by pulling all rows to code (there's ~25000), and then doing the merge manually. It seems like it should be possible in sql though.

From a code perspective, it looks like a fold operation, so would a WITH RECURSIVE cte help me here?

Answer 1

try this :

  select max(bad_id),
    split_part(string_agg(a,'__SPLITER__' order by bad_id DESC),'__SPLITER__',1)
    ,split_part(string_agg(b,'__SPLITER__' order by bad_id DESC),'__SPLITER__',1)  
  from foo group by real_id

If a and b are timestamp :

    select max(bad_id),
        split_part(string_agg(a::character varying,'__SPLITER__' order by bad_id DESC),'__SPLITER__',1)::timestamp,
        split_part(string_agg(b::character varying,'__SPLITER__' order by bad_id DESC),'__SPLITER__',1)::timestamp  
    from foo group by real_id

Same for integer : split_part(string_agg(a::character varying ...,1)::integer