A Handful of Useful Features in PostgreSQL & SQL


This post is by Hashrocket from Hashrocket


Click here to view on the original site: Original Post




Last week, I was called to jump on a Rails project that needed some performance optimizations. Our chosen approach was to take a bulk lookup-and-update process out of ActiveRecord land and move everything into Postgres. The performance improvement was huge for large record sets. At the high end, we saw request times go from > 5 minutes to sub 2 seconds.

I had used SQL and Postgres before, but I wasn't totally familiar with its full set of features. At first, I paired with a coworker who has a deeper knowledge of Postgres. After a few days of writing queries and refactoring the test suite, everything was green and I was ready to deploy to staging for testing.

Here's some interesting SQL and PostgreSQL things I learned along the way.

CTE's

Although not exclusive to PostgreSQL, CTE's, or Common Table Expressions, are a way to write reusuable queries in a system.

Let's say we have 2 tables – books and authors.

Books Table

id title author_id genre
1 Digital Fortress 1 thriller
2 The Da Vinci Code 1 thriller
3 Harry Potter and The Chamber of Secrets 2 fantasy

Authors Table

id name age
1 Dan Brown 54
2 J. K. Rowling 53

We can write a really simple CTE to get the books with the authors:

with authors_and_books as (
    SELECT
        b.id as book_id,
        b.title,
        a.name as author_name
    FROM books b
    JOIN authors a on b.author_id = a.id;
);

Then, selecting from our CTE, we get the following:

SELECT * FROM authors_and_books;
book_id title author_name
1 Digital Fortress Dan Brown
2 The Da Vinci Code Dan Brown
3 Harry Potter and The Chamber of Secrets J. K. Rowling

This example is simple but you can see how this can really come in handy when you need to reuse complex queries.


Temp Tables (PostgreSQL)

Another really useful database feature is the ability to create temporary tables. In my case, this was really useful for staging "unprocessed" data that was posted from the front end. To create a temp table, you'll need to define its schema like your normal create table .. statement.

CREATE TEMPORARY TABLE temp_isbns (
    title varchar(255) NOT NULL,
    isbn varchar(255) NOT NULL,
    author_name varchar(255) NOT NULL
) ON COMMIT DROP;

The important part here is the ON COMMIT at the end. You'll need to tell Postgres how to handle the temporary table at the end of the transaction block. DROP tells Postgres to drop the temporary table at the end of the transaction block. The Postgres docs describe more of the ON COMMIT options.


COALESCE

The COALESCE function returns the first non-null value passed to it. This function accepts an unlimited number of arguments and it returns the first non-null argument, evaluated from left-to-right.

Here's a few examples of what that might look like:

Coalesce Example 1

select coalesce(null, 1);
coalesce
1

Coalesce Example 2

select coalesce(null, null, 1, null);
coalesce
1

Coalesce Example 3

select coalesce(2, null, 1);
coalesce
2

Upserts

Upserting, or updating-and-inserting, is a superful feature in Postgres. It allows you to handle inserts with conflict resolution if a record already exists in the database.

Remember our books table?

Books Table

id title author_id genre
1 Digital Fortress 1 thriller
2 The Da Vinci Code 1 thriller
3 Harry Potter and The Chamber of Secrets 2 fantasy

Upserts Example 1

We'll insert some records into books but if the book exists, we'll choose to do nothing:

INSERT INTO books (id, title, author_id, genre) VALUES
    (3, 'Harry Potter and The Chamber of Secrets', 2, 'fantasy'),
    (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy')
ON CONFLICT DO NOTHING;

Now let's look at our table:

SELECT * FROM books;
id title author_id genre
1 Digital Fortress 1 thriller
2 The Da Vinci Code 1 thriller
3 Harry Potter and The Chamber of Secrets 2 fantasy
4 Harry Potter and The Half Blood Prince 2 fantasy

Upserts Example 2

Other other hand, let's insert records into books and update the record with the corresponding ID. Per the Postgres docs, you'll need to use the EXCLUDED table to reference the values proposed for insertion.

INSERT INTO books (id, title, author_id, genre) VALUES
    (3, 'Harry Potter and The Goblet of Fire', 2, 'fantasy'),
    (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy')
ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title;

Looking at our table one last time:

SELECT * FROM books;
id title author_id genre
1 Digital Fortress 1 thriller
2 The Da Vinci Code 1 thriller
3 Harry Potter and The Goblet of Fire 2 fantasy
4 Harry Potter and The Half Blood Prince 2 fantasy

Thanks for following along with this blog post. If there's a particular Postgres feature you'd like to hear about, feel free to reach out at andrew.vogel@hashrocket.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.