SQL WITH Statement: INSERTT with RETURNING

with <span>new_order</span> as(
  <span class="m_6830738492873100482gmail-hljs-keyword">insert</span> <span class="m_6830738492873100482gmail-hljs-keyword">into</span> orders(email, total) 
  <span class="m_6830738492873100482gmail-hljs-keyword">values</span> (<span class="m_6830738492873100482gmail-hljs-string">'<a target="_blank">rob@bigmachine.io</a>'</span>,<span class="m_6830738492873100482gmail-hljs-number">100.00</span>) 
  <span><span class="m_6830738492873100482gmail-hljs-keyword">returning</span> *</span>
), <span>new_items</span> <span class="m_6830738492873100482gmail-hljs-keyword">as</span> (
  <span class="m_6830738492873100482gmail-hljs-keyword">insert</span> <span class="m_6830738492873100482gmail-hljs-keyword">into</span> order_items(order_id, sku, price)
  <span class="m_6830738492873100482gmail-hljs-keyword">select</span> <a href="http://new_order.id/" target="_blank" rel="noopener" data-saferedirecturl="https://www.google.com/url?hl=en&q=http://new_order.id&source=gmail&ust=1535028417026000&usg=AFQjCNHINX_M_OT015clggcEFQ5qv3D__A">new_order.id</a>, <span class="m_6830738492873100482gmail-hljs-string">'imposter-single'</span>,<span class="m_6830738492873100482gmail-hljs-number">30.00</span>
  <span class="m_6830738492873100482gmail-hljs-keyword">from</span> <span>new_order</span>
  <span><span class="m_6830738492873100482gmail-hljs-keyword">returning</span> *</span>
), new_downloads <span class="m_6830738492873100482gmail-hljs-keyword">as</span> (
  <span class="m_6830738492873100482gmail-hljs-keyword">insert</span> <span class="m_6830738492873100482gmail-hljs-keyword">into</span> downloads(order_id, order_item_id)
  <span class="m_6830738492873100482gmail-hljs-keyword">select</span> <a href="http://new_order.id/" target="_blank" rel="noopener" data-saferedirecturl="https://www.google.com/url?hl=en&q=http://new_order.id&source=gmail&ust=1535028417026000&usg=AFQjCNHINX_M_OT015clggcEFQ5qv3D__A">new_order.id</a>, <a href="http://new_items.id/" target="_blank" rel="noopener" data-saferedirecturl="https://www.google.com/url?hl=en&q=http://new_items.id&source=gmail&ust=1535028417026000&usg=AFQjCNF5IIrwou-O6OE2uShJL0_39JsPEA">new_items.id</a> 
  <span class="m_6830738492873100482gmail-hljs-keyword">from</span> <span>new_order</span>, <span>new_items</span>
  <span><span class="m_6830738492873100482gmail-hljs-keyword">returning</span> *</span>
)

<span class="m_6830738492873100482gmail-hljs-keyword">select</span> * <span class="m_6830738492873100482gmail-hljs-keyword">from</span> new_downloads;

I tack on a <span style="color: #333333;">returning *</span> from my insert statement for <span style="color: #333333;">order_items</span> and then I can use that to generate the downloads in a third query, this time using a <span style="color: #333333;">select</span> for the insert.

How to do histograms in PostgreSQL

SELECT * FROM histogram($table_name_or_subquery, $column_name);

. . . to give sweet results like this, in a check of the distribution of 2016 political contributions in Vermont:

fec=# SELECT * FROM histogram('(SELECT * FROM small_donors_vt LIMIT 50000)', 'transaction_amt');

 bucket |   range   | freq |       bar       
--------+-----------+------+-----------------
      1 | [0,9]     | 2744 | ******
      2 | [10,19]   | 5630 | *************
      3 | [20,29]   | 6383 | ***************
      4 | [30,39]   | 1290 | ***
      5 | [40,49]   |  369 | *
      6 | [50,59]   | 3541 | ********
      7 | [60,69]   |  174 | 
      8 | [70,79]   |  313 | *
      9 | [80,89]   |  171 | 
     10 | [90,99]   |   65 | 
     11 | [100,109] | 2363 | ******
     12 | [110,119] |   51 | 
     13 | [120,129] |  115 | 
     14 | [130,139] |   32 | 
     15 | [140,146] |   11 | 
     16 | [150,159] |  187 | 
     17 | [160,169] |   24 | 
     18 | [170,177] |   33 | 
     19 | [180,189] |   19 | 
     20 | [191,199] |   24 | 
     21 | [200,200] |  795 | **

Find the number of the longest continuously rising days for a stock

Today I want to react to an article that claims that Relational Algebra Is the Root of SQL Problems in which the author hand-waves the following position:

SQL becomes more a hindrance to data manipulation than an efficient tool. SQL’s greatest problem isn’t in the implementation level, but at its theory foundation. The problem can’t be solved by application optimization. Relational algebra isn’t sophisticated enough for handling the complicated data manipulation scenarios.

Datasette: instantly create and publish an API for your SQLite databases

A key feature of datasette is that the API it provides is very deliberately read-only. This provides a number of interesting benefits:

 

  • It lets us use SQLite in production in high traffic scenarios. SQLite is an incredible piece of technology, but it is rarely used in web application contexts due to its limitations with respect to concurrent writes. Datasette opens SQLite files using the immutable option, eliminating any concurrency concerns and allowing SQLite to go even faster for reads.
  • Since the database is read-only, we can accept abritrary SQL queries from our users!