#pgfoundry

PostgreSQL 10 has been release into production, so I wanted to give it a try in Linux Mint.  I have also installed Java 9, so I am playing just behind the bleeding edge as of Dec 12, 2017.  I installed PostgreSQL from the installer I got from the download page at EnterpriseDB.  I ran the installer, and grabbed a copy of the Pagila database (a sample database from pgFoundry) and unzipped it.  I created the database in pgAdmin 4, which is much prettier and more powerful than pgAdmin 3.  I was then able to install with pgsql using commands like for each of the 3 files in the download       /opt/PostgreSQL/10/bin/psql -U postgres -d pagila -f ~/Downloads/pagila-0.10.1/pagila-data.sql I used the order pagila-schema.sql, pagila-insert-data.sql, pagila-data.sql.

I was feeling a bit rusty on window functions, so I tried to find the top-selling movies by MPAA Rating.   The query is:

select title, rating, sales from (   select f.title,  f.rating, sum(p.amount) sales, rank() over (partition by rating  order by sum(p.amount) desc)   from film f   join inventory i using(film_id)   join rental r using(inventory_id)   join payment p using(rental_id)   group by f.film_id ) movies where rank=1 order by rating;

There are a few fun features in this query.  The joins use the ‘using’ keyword for the joins.  This rewards you for following your naming conventions by reducing typing, and I will argue, the chance of making a thinko (eg. f.film_id=i.inventory_id).  This ran on a home PC in 51ms, summing over 16,049 rows and joining 4 tables.  Nice.

The results are the 5 top selling movies by MPAA Rating.  Since the sample database defined an MPAA_Rating type, the movies ordered G, PG, PG-13, R, NC-17.  Which is what I wanted, but which you don’t get if it ordered alphabetically.