PostgreSQL 10 - kicking the tires
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.
So, I was please that it installed and ran with little hassle.
