CODEWITHSUNDEEP
postgresql
Tomas Greif
Tomas Greif

Reputation: 22653

Shift data column-wise

I have a table with date columns, an example:

a           b           c           d           e  
            09-09-2015  09-08-2015              09-12-2015
09-08-2015              09-11-2015  09-12-2015  
09-08-2015              09-11-2015              09-12-2015
09-08-2015  09-09-2015              09-11-2015  09-12-2015
09-08-2015  09-09-2015  09-10-2015  09-11-2015  09-12-2015

Is there a way how I could create a new table where all date values are shifted to the left, so there are no gaps? For my data that would be:

a           b           c           d           e  
09-09-2015  09-08-2015  09-12-2015
09-08-2015  09-11-2015  09-12-2015  
09-08-2015  09-11-2015  09-12-2015
09-08-2015  09-09-2015  09-11-2015  09-12-2015
09-08-2015  09-09-2015  09-10-2015  09-11-2015  09-12-2015

Sample data:

-- drop table if exists tst;

create table tst (
    a date,
    b date,
    c date,
    d date,
    e date);

insert into tst values 
    (NULL, current_date + 1, current_date, NULL, current_date + 4),
    (current_date, NULL, current_date + 3, current_date + 4, NULL),
    (current_date, NULL, current_date + 3, NULL, current_date + 4),
    (current_date, current_date + 1, NULL, current_date + 3, current_date + 4),
    (current_date, current_date + 1, current_date + 2, current_date + 3, current_date  + 4)     
    ;

I thought it would work with simple update:

update tst set a = case when a is not null then a else
            case    when b is not null then b
                when c is not null then c
                when d is not null then d
                when e is not null then e end
            end;

This is not a way, because with column b I would need to handle the situation where the value has been moved to a previously.

Upvotes: 0

Views: 267

Answers (1)

Marc P.
Marc P.

Reputation: 702

If it's ok to create a new table, you could transform each row of tst into a row which just contains an array of dates. Then you could remove NULLs with array_remove(anyarray, NULL) and fill a new table with the resulting array.

CREATE OR REPLACE VIEW tst_array AS
    SELECT
        array_remove(ARRAY[a,b,c,d,e], NULL) AS dates
    FROM
        tst;

SELECT
    dates[1] AS a,
    dates[2] AS b,
    dates[3] AS c,
    dates[4] AS d,
    dates[5] AS e
FROM
    tst_array;

Upvotes: 2

Related Questions