May 2009 Archives

I just read the following in a brochure:

(Such and such) is optimized for label printing with the ability to print 2” per second in black and 10’ per minute with full color.

Unit conversion in comparisons can be used to make a stat seem better than it really is (or worse, if that’s your goal). Color printing is usually slower than B&W printing (on account of there being a lot more data/work involved), and I’m curious how much slower it is, but in this case, I think someone just messed up:

• 10’ = 120”
• 1 minute = 60 seconds
• 120” per 60 seconds = 2” per second

I wonder if it was supposed to be 10” per minute instead of 10’…

Update a table according to the results of another query (slightly simplified):

UPDATE letters AS final
   SET preview_file_id = outer.preview_file_id
  FROM (SELECT letter_id,
               (SELECT preview_file_id
                  FROM inserts AS i
                 WHERE i.insert_id = inner.insert_id) AS preview_file_id
          FROM (SELECT letter_id,
                       MIN(insert_id) AS insert_id
                  FROM letters AS l
                  JOIN letter_insert_ref USING (letter_id)
                  JOIN inserts AS inner_i USING (insert_id)
                 WHERE l.preview_file_id IS NULL
                   AND inner_i.preview_file_id IS NOT NULL
                   AND l.submitted > NOW() - '1 month'::INTERVAL
              GROUP BY letter_id) AS inner) AS outer
 WHERE final.letter_id = outer.letter_id;

Or, in something resembling English, “In the letters table, set the preview file according to the following list of recent letters and the associated preview files for the first insert of each of those letters.”

There might be a way to simplify the two sub-queries into just one using grouping, ordering, and limiting, but this worked well enough for a one-off query.

Background: I thought I no longer needed the previewfileid field in letters, so I deprecated it recently, and stopped setting it. The API layer had a workaround, but there’s still a program that uses a database view that needs the previewfileid to be set in letters, so I needed to revert that change and back-fill the previewfileids that didn’t get set. UPDATE FROM to the rescue!

(Also, if anyone has any layout suggestions on how to make complex SQL more readable, I’m all ears. I’ve been center-aligning to the keyword for years just because I haven’t found anything better.)