Postgres OnLine Journal (Entries tagged as aggregates)

True or False every which way

nospam@example.com (Leo Hsu and Regina Obe) — Wed, 25 Jan 2012 15:55:00 -0500

PostgreSQL has aggregate functions called bool_and and bool_or which it's had for as far back as I can remember. What do they do? given rows of premises (things that resolve to booleans), bool_and will return true if all of the premises are true. Similarly bool_or will return true if any of the premises in the set of rows is true. What if however your boolean expressions are not in rows, but instead passed in as a sequence of arbitrary statements of questionable fact. We want a function like bool_or or bool_and that takes an arbitrary number of boolean arguments. Are there functions that fit the bill. Indeed there are, but they don't scream out and say I work with booleans because they fit into a class of function we discussed in The wonders of Any Element and that also happen to be variadic functions. These are none other than greatest and least and they are old timer functions that you can find in most versions of PostgreSQL. We'll demonstrate how to use all 4 with booleans in this article. It must be said that greatest and least are much more useful when applied to other data types like dates and numbers, but we were amused at the parallel with booleans.

Side note: we've started to write our book on PostgreSQL that will be published by O'Reilly. We hope to finish this book within the next 5-7 months but have preliminary e-Book drafts before then for early purchasers to scan. The focus of the book will be PostgreSQL 9.1 with some highlights the upcoming PostgreSQL 9.2. Of course oldie but goodie topics are in the book too. It's a thrill to be writing again.

Continue reading "True or False every which way"

How to create an n-column table really fast

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 01 Nov 2011 22:31:00 -0400

Ever have the need to create a holding table say spreadsheet data with say 100 columns. You need to create a table to hold this stuff. Or perhaps you were feeling in a sadist mood and wanted to abuse your PostgreSQL database to see how many columns you can create in a table of a specific data type. Here is a quick script to do it:


-- 8.4+ syntax
SELECT 'CREATE TABLE data_import('
|| array_to_string(array_agg('field' || i::text || ' varchar(255)'), ',') || ');'
FROM generate_series(1,10) As i;

-- 9.0+ syntax (string_agg was introduced in 9.0)
SELECT 'CREATE TABLE data_import('
|| string_agg('field' || i::text || ' varchar(255)', ',') || ');'
FROM generate_series(1,10) As i;

Both variants will return output that looks like this:

CREATE TABLE data_import(field1 varchar(255),field2 varchar(255),field3 varchar(255),field4 varchar(255)
    ,field5 varchar(255),field6 varchar(255),field7 varchar(255)
    ,field8 varchar(255),field9 varchar(255),field10 varchar(255));

Now if you want it to also execute because you are running it as part of an sql script, you could wrap it in an anonymous function.


 ---wrap it in an anonymous function to run
 -- requires PostgreSQL 9.0+
DO language 'plpgsql'
$$
DECLARE var_sql text :=  'CREATE TABLE data_import('
    || string_agg('field' || i::text || ' varchar(255)', ',') || ');'
    FROM generate_series(1,10) As i;
BEGIN
    EXECUTE var_sql;
END;
$$ ;

PostgreSQL Array: The ANY and Contains trick

nospam@example.com (Leo Hsu and Regina Obe) — Thu, 27 Oct 2011 01:09:00 -0400

One of the main features I love about PostgreSQL is its array support. This is a feature you won't find in most relational databases, and even databases that support some variant of it, don't allow you to use it as easily. It is one of the features that makes building aggregate functions wicked easy in PostgreSQL with no messy compiling required. Aside from building aggregate functions, it has some other common day uses. In this article, I'll cover two common ways we use them which I will refer to as the ANY and Contains tricks.

I like to think of this approach as YeSQL programming style: how SQL can be augmented by more complex data types and index retrieval mechanisms. Arrays and many other data types (spatial types, keyvalue (hstore), ltree etc) are far from relational structures, yet we can query them easily with SQL and can even relate them.

Continue reading "PostgreSQL Array: The ANY and Contains trick"

PLPython Part 5: PLPython meets PostgreSQL Multi-column aggregates and SVG plots

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 13 Mar 2009 08:28:00 -0400

In our PLPython Part 4: PLPython meets aggregates we demonstrated the power of PostgreSQL in combination with PLPython to create a sequence of ascii plots by using the power of aggregation. Our friend Simon Greener over at Spatial Db Advisor told me the example was clever but ascii plots was so 70ish and ugly, why didn't I do SVG plots? He felt people being predominantly visual would be caught up in the ugliness of Ascii plots and miss the point. At least Bob found them neat and can appreciate the elegance in simplicity, thanks Bob..

To make it up to Simon, we shall demonstrate 2 features we left out in our prior article.

Pretty SVG graphs -- so I don't have to hear more about SVG from Simon and how I'm caught in the 70s.
Ability to plot a schedule -- schedule item, start date, end date using a multi-column aggregate function. Here is a good use for multi-column aggregates or at least I think so. (Yeh for Multi-column aggregates)

If you are into spatial processing in the database or how many ways you can play with XML particularly Oracle Locator, Oracle Spatial, PostGIS and SQL Server 2008, we highly recommend Simon's articles.

Continue reading "PLPython Part 5: PLPython meets PostgreSQL Multi-column aggregates and SVG plots"

PLPython Part 4: PLPython meets aggregates

nospam@example.com (Leo Hsu and Regina Obe) — Thu, 05 Feb 2009 19:36:00 -0500

In our August 2008/ September 2008 issue we demonstrated the power of PostgreSQL to create median and MS Access-like first and last aggregate functions in SQL language. In this article we shall demonstrate how to create aggregates with Python. We shall call this function agg_plot. What it will do is plot each grouping of data and return a plot for each grouping. The steps we covered in those articles can be applied here.

For this part, we shall use the same library we discussed in PLPython Part 3: Using custom classes, pulling data from PostgreSQL.

Continue reading "PLPython Part 4: PLPython meets aggregates"

More Aggregate Fun: Who's on First and Who's on Last

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 12 Aug 2008 22:58:00 -0400

Microsoft Access has these peculiar set of aggregates called First and Last. We try to avoid them because while the concept is useful, we find Microsoft Access's implementation of them a bit broken. MS Access power users we know moving over to something like MySQL, SQL Server, and PostgreSQL often ask - where's first and where's last? First we shall go over what exactly these aggregates do in MS Access and how they are different from MIN and MAX and what they should do in an ideal world. Then we shall create our ideal world in PostgreSQL.

Continue reading "More Aggregate Fun: Who's on First and Who's on Last"

Build Median Aggregate Function in SQL

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 10 Aug 2008 21:10:00 -0400

One of the things we love most about PostgreSQL is the ease with which one can define new aggregate functions with even a language as succinct as SQL. Normally when we have needed a median function, we've just used the built-in median function in PL/R as we briefly demonstrated in Language Architecture in PostgreSQL.

If all you demand is a simple median aggregate function ever then installing the whole R statistical environment so you can use PL/R is overkill and much less portable.

In this article we will demonstrate how to create a Median function with nothing but the built-in PostgreSQL SQL language, array constructs, and functions.

Continue reading "Build Median Aggregate Function in SQL"