Window, window on the wall …
November 20th, 2012 by depesz | Tags: aggregate, array_agg, postgresql, primer, string_agg, window, window functions | 9 comments »

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 ...

Getting unique elements
April 19th, 2010 by depesz | Tags: aggregate, distinct, group by, postgresql, unique | 25 comments »

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: select a.* from ...

Waiting for 9.0 – string_agg
February 17th, 2010 by depesz | Tags: aggregate, pg85, pg90, postgresql, string_agg | 5 comments »

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 ...

Waiting for 8.5 – ordered aggregates
January 6th, 2010 by depesz | Tags: aggregate, array_agg, order, pg85, pg90, postgresql | 3 comments »

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 ...

Calculating median
July 13th, 2009 by depesz | Tags: aggregate, dim, irc, plpgsql, postgresql, rhodiumtoad, sql | 1 comment »

Today, on irc (#postgresql on Dim mentioned about writing median calculation code. It got me thinking, and consequently writing my version of median calculation code.

Waiting for 8.4 – window functions
January 21st, 2009 by depesz | Tags: aggregate, avg, cumulative sum, groups, partitions, pg84, postgresql, rownum, sum, window functions | 2 comments »

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 ...

Waiting for 8.4 – array aggregate and array unpacker
November 14th, 2008 by depesz | Tags: aggregate, arrays, array_agg, pg84, postgresql, unnest | 4 comments »

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.

  • Popular Posts

    • Waiting for 9.3 – Create a psql command \gset to store query results into psql variables 0 comment(s) | 547 view(s)
    • Jak obsłużyć klienta, na przykładzie firmy Kurtmedia 9 comment(s) | 264 view(s)
    • Why is UPSERT so complicated? 0 comment(s) | 238 view(s)
    • Adding plans to from your psql 2 comment(s) | 217 view(s)
    • “FATAL: Ident authentication failed”, or how cool ideas get bad usage schemas 0 comment(s) | 187 view(s)
    • CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03 0 comment(s) | 181 view(s)
    • Understanding postgresql.conf : log* 0 comment(s) | 152 view(s)
    • Index on (a,b) vs. (b,a)? 0 comment(s) | 150 view(s)
    • Write Ahead Log + Understanding postgresql.conf: checkpoint_segments, checkpoint_timeout, checkpoint_warning 0 comment(s) | 117 view(s)
    • Waiting for 8.4 – RETURN QUERY EXECUTE and cursor_tuple_fraction 0 comment(s) | 110 view(s)
  • postgresql

    • Documentation
    • Explain Analyze analyzer
    • IRC help channel
    • Mailing Lists search
    • PG Planet
    • PostgreSQL Home Page
  • About me

    • CPAN
    • Hardware for tests
    • LinkedIn
    • StackOverflow
  • Theme

  • is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.