Explaining the unexplainable – part 3

In previous post in the series I wrote about how to interpret single line in explain analyze output, it's structure, and later on described all basic data-getting operations (nodes in explain tree).

Today, we'll move towards more complicated operations.

Continue reading Explaining the unexplainable – part 3

Waiting for 9.3 – JSON generation improvements.

On 10th of March, Andrew Dunstan committed patch:

JSON generation improvements.
This adds the following:
    json_agg(anyrecord) -> json
    to_json(any) -> json
    hstore_to_json(hstore) -> json (also used as a cast)
    hstore_to_json_loose(hstore) -> json
The last provides heuristic treatment of numbers and booleans.
Also, in json generation, if any non-builtin type has a cast to json,
that function is used instead of the type's output function.
Andrew Dunstan, reviewed by Steve Singer.
Catalog version bumped.

Continue reading Waiting for 9.3 – JSON generation improvements.

Window, window on the wall …

And maybe not on the wall, but instead in your SQLz, eating your data.

But a bit more seriously. Ever since PostgreSQL 8.4 we have window functions, but still I see people which do not know it or are wary to use it.

That's why I decided to write a piece on window functions. How they work and what they can be used for.

Continue reading Window, window on the wall …

Getting unique elements

Let's assume you have some simple database with “articles" – each article can be in many “categories". And now you want to get list of all articles in given set of categories.

Standard approach:

    articles as a
    join articles_in_categories as aic on a.id = aic.article_id
    aic.category_id in (14,62,70,53,138)

Will return duplicated article data if given article is in more than one from listed categories. How to remove redundant rows?

Continue reading Getting unique elements

Waiting for 9.0 – string_agg

On 1st of Februyary, Takahiro Itagaki committed a patch by Pavel Stehule which adds string_agg aggregate:

Log Message:
Add string_agg aggregate functions. The one argument version concatenates
the input values into a string. The two argument version also does the same
thing, but inserts delimiters between elements.
Original patch by Pavel Stehule, reviewed by David E. Wheeler and me.

Continue reading Waiting for 9.0 – string_agg

Waiting for 8.5 – ordered aggregates

On 15th of December Tom Lane committed patch by Andrew Gierth (aka RhodiumToad), which adds interesting capability:

Log Message:
Support ORDER BY within aggregate function calls, at long last providing a
non-kluge method for controlling the order in which values are fed to an
aggregate function.  At the same time eliminate the old implementation
restriction that DISTINCT was only supported for single-argument aggregates.
Possibly release-notable behavioral change: formerly, agg(DISTINCT x)
dropped null values of x unconditionally.  Now, it does so only if the
agg transition function is strict; otherwise nulls are treated as DISTINCT
normally would, ie, you get one copy.
Andrew Gierth, reviewed by Hitoshi Harada

Continue reading Waiting for 8.5 – ordered aggregates

Waiting for 8.4 – window functions

A long overdue post about new functionality. At this moment it is no longer such new, as it was committed on 28th of December (yes, I know, I should have written it earlier, Sorry).

On this day Tom Lane committed patch by Hitoshi Harada which adds support for so called window functions:

Support window functions a la SQL:2008.
Hitoshi Harada, with some kibitzing from Heikki and Tom.

Continue reading Waiting for 8.4 – window functions

Waiting for 8.4 – array aggregate and array unpacker

Finally, we got very important addons to PostgreSQL, which help with dealing with arrays.

It solves a lot of problems, which were usually solved with standard cookbook code, which was in faqs, blog posts and number of examples on irc.

Continue reading Waiting for 8.4 – array aggregate and array unpacker