Postgres Queries: Learning More

The most sophisticated query I have used these major features:

  • INNER JOIN
    • To combine fields from separate tables, as if they’re in one table
  • ON
    • The property or attribute that is the hinge for a JOIN
  • WHERE
    • The conditions that must be met for the new one table
  • LIMIT
    • To select only a subset of results

I’m checking a query discussed in an XBRL US Webinar, it includes other functions. Let’s learn more about these… :

  • JOIN (vs INNER JOIN)
    • What’s the difference? This diagram is helpful
    • Remember, there are: RIGHT, LEFT, INNER, OUTER, and … plain!
    • See Postgres documentation of join types, here
  • CASE  & WHEN
    • Don’t need to SELECT properties as-is
      • can modify them (see New operators, below)
      • based on conditions
    • CASE marks the beginning of a series of conditional tests, like a switch statement. The series consists of:
      • WHEN is like an if, (or an else-if, when there are multiple WHEN’s); it is followed by a conditional expression
      • THEN follows every conditional expression; THEN is like the {block} of text executed, if the WHEN condition is satisfied
    • ELSE caps off the series, so you know the last {block} if no WHENs are met
    • END marks the end of the series
    • Like CASE WHEN period_instant is null THEN c.period_start || ‘-‘ || c.period_end ELSE c.period_instant::text END AS period
  • New operators
    • BETWEEN
      • A WHERE operator, which is equivalent to min>=x<=max
    • ||
      • A concatenation operator, like context.period_start || ‘-‘ || context.period_end
      • Notice strings in single-quotes, are okay
    • ::
      • A conversion/casting operator, like context.period_instant::text
      • Note that this datatype is called text, vs string. This is common in queries.
    • in
      • A membership operator, like WHERE qname.local_name in (‘assets’, ‘revenues’)
      • Note that the list uses Python tuple syntax
  • Custom  Functions
    •  the XBRL US Database specifically has functions to encapsulate complex but common operations:
      • uom(fact.unit_id)
        • A value’s unit-of-measure
      • list_dimensional_qualifications(c.context_id)
        • How many dimensions the data is trying to model
      • is_base(qname.namespace)
        • Else the element is extended
  • Aliasing
    • Using “as” to name a SELECT, like q.local_name as concept
    • Following the table name with a nickname, like JOIN qname q
    • And understanding that these two relate;
      • JOIN qname q (at bottom of query)
      • Affects SELET q.local_name as concept (at top of query)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: