title: How do I efficiently select the previous non-null value?
body: <p>I have a table in Postgres that looks like this:</p>
  
  <pre><code># select * from p;
   id | value 
  ----+-------
    1 |   100
    2 |      
    3 |      
    4 |      
    5 |      
    6 |      
    7 |      
    8 |   200
    9 |          
  (9 rows)
  </code></pre>
  
  <p>And I'd like to query to make it look like this:</p>
  
  <pre><code># select * from p;
   id | value | new_value
  ----+-------+----------
    1 |   100 |    
    2 |       |    100
    3 |       |    100
    4 |       |    100
    5 |       |    100
    6 |       |    100
    7 |       |    100
    8 |   200 |    100
    9 |       |    200
  (9 rows)
  </code></pre>
  
  <p>I can already do this with a subquery in the select, but in my real data I have 20k or more rows and it gets to be quite slow. </p>
  
  <p>Is this possible to do in a window function? I'd love to use lag(), but it doesn't seem to support the <a href="http://postgresql.1045698.n5.nabble.com/Request-for-Patch-Feedback-Lag-amp-Lead-Window-Functions-Can-Ignore-Nulls-td5749395.html">IGNORE NULLS</a> option.</p>
  
  <pre><code>select id, value, lag(value, 1) over (order by id) as new_value from p;
   id | value | new_value
  ----+-------+-----------
    1 |   100 |      
    2 |       |       100
    3 |       |      
    4 |       |
    5 |       |
    6 |       |
    7 |       |
    8 |   200 |
    9 |       |       200
  (9 rows)
  </code></pre>