Re: Update with last known location?

James David Smith wrote
> INSERT INTO test_data VALUES
> ('1', '2012-01-01 07:00', '1'),
> ('1', '2012-01-01 07:01', '1'),
> ('1', '2012-01-01 07:02', '1'),
> ('1', '2012-01-01 07:03', NULL), -- null should be replaced with 1
> ('1', '2012-01-01 07:04', NULL), -- null should be replaced with 1
> ('1', '2012-01-01 07:05', '5'),
> ('1', '2012-01-01 07:06', '5'),
> ('1', '2012-01-01 07:07', '5'),
> ('1', '2012-01-01 07:08', NULL), -- null should be replaced with 5
> ('1', '2012-01-01 07:09', NULL), -- null should be replaced with 5
> ('1', '2012-01-01 07:10', NULL), -- null should be replaced with 5
> ('1', '2012-01-01 07:11', NULL), -- null should be replaced with 5
> ('2', '2013-05-02 07:12', '24'),
> ('2', '2013-05-02 07:13', '24'),
> ('2', '2013-05-02 07:14', '24'),
> ('2', '2013-05-02 07:15', NULL), -- null should be replaced with 24
> ('2', '2013-05-02 07:16', NULL), -- null should be replaced with 24
> ('2', '2013-05-02 07:17', '44'),
> ('2', '2013-05-02 07:18', '44'),
> ('2', '2013-05-02 07:19', NULL), -- null should be replaced with 44
> ('2', '2013-05-02 07:20', '4'),
> ('2', '2013-05-02 07:21', '4'),
> ('2', '2013-05-02 07:22', '4');

This specific problem has two solutions.

1. Create a custom aggregate that maintains the last non-null value
encountered and returns it as a final value.

2. More slowly, but less complexly, use array_agg to capture all prior
values of the data in question.  Then pass that array into a function that
unnests the array, removes the Nulls, reverses the order, and applies limit
1.

For both solutions you will need to construct a window clause with an order
by.

Examples exists in the mailing list archive.  Recently I can recall Merlin
and myself posting these but cannot go find them at this moment.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Update-with-last-known-location-tp5788966p5789708.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.