The Art of PostgreSQL/

...

/

Back to Discovering SQL

Back to Discovering SQL

Learn how to use SQL queries to get the same outcomes as an application.

We'll cover the following...

Of course, it’s possible to implement the same expectations that we have seen in the previous lesson with a single SQL query without any application code being spent on solving the problem.

A single query vs. application code

The following query will do the same job as that done by the application code.

Press the “Run” button in the following widget to see the output of the code.

PostgreSQL
begin;
create table factbook
 (
   year    int,
   date    date,
   shares  text,
   trades  text,
   dollars text
 );
-- datestyle of the database to ISO, MDY
\copy factbook from '/usercode/factbook.csv' with delimiter E'\t' null ''
alter table factbook
   alter shares
    type bigint
   using replace(shares, ',', '')::bigint,
   alter trades
    type bigint
   using replace(trades, ',', '')::bigint,
   
   alter dollars
    type bigint
   using substring(replace(dollars, ',', '') from 2)::numeric;
commit;
\set start '2017-02-01'
  select cast(calendar.entry as date) as date,
         coalesce(shares, 0) as shares,
         coalesce(trades, 0) as trades,
         to_char(
             coalesce(dollars, 0),
             'L99G999G999G999'
         ) as dollars
    from /*
          * Generate the target month's calendar then LEFT JOIN
          * each day against the factbook dataset, so as to have
          * every day in the result set, whether or not we have a
          * book entry for the day.
          */
         generate_series(date :'start',
                         date :'start' + interval '1 month'
                                       - interval '1 day',
                         interval '1 day'
         )
         as calendar(entry)
         left join factbook
                on factbook.date = calendar.entry
order by date;

Code explanation

In this query, we use several basic SQL and PostgreSQL techniques that we might be discovering for the first time:

  • SQL accepts comments written either in -- comment style, running from the opening to the end of the line, or C-style with a /* comment */ ...