A Problem Postgres Can Solve Easily

Let’s say you need to insert a record into a table and then use that record immediately to push data into another table. This happens a lot with parent/child relationships (think about users and roles – add a user, add to a role, and log it – 3 queries!) and, typically, developers will shove that into the app layer and let Rails/Node/ASP.NE T execute multiple queries.

But you don’t have to! Just know you some SQL and you’re good to go.

Common Table Expressions

These are not new and have been around for a while – and yes SQL Server fans you can do what you’re about to see in SQL Server, however you won’t have the syntactic niceties.

First, let’s create some tables in a demo database:

create table things(   id serial primary key,   name varchar(50) );  create table thing_logs(   id serial primary key,   thing_id int not null references things(id),   entry varchar(50) ); 

In the example above you can see the use of serial, a great shortcut for creating a primary key. This is example is pretty stupid, but hopefully it gets the point across.

Let’s insert some data with a common table expression. I’ll insert the record, pull the record out, and insert the new values into the logs table:

with inserted as (   insert into things(name)   values('Rob') returning * ) insert into thing_logs(thing_id,entry) select inserted.id, 'New thing created: ' || inserted.name from inserted; 

If you’ve never used them, Common Table Expressions (CTEs) can look quite foreign at first. Basically, they create a table on the fly that you can then query against. Here I’m using Postgres syntactic shortcut for returning the inserted record: returning *. If I only wanted the id I could use returning id.

I wrap in a WITH inserted and I can use it in a subsequent query (I can call it whatever I like). In this case I drop an entry into the thing_logs table.

But why would I do this?

Two reasons: performance and transactions. In the above query performance doesn’t really matter, but as your database grows you’ll want to streamline the connections to the database and make them as reasonable as you can.

Transactions are something many developers just don’t think about until they realize they’re not trapping all the data they should. In this case we’re only executing two writes – but in the real world you might have 5 or 6. Perhaps when a user is created.

You can use CTEs for this as well (UPDATE: I originally wrapped this with a BEGIN/COMMIT but CTEs, even chained like this, encompass a transaction):

with new_user as(   insert into users(email,created_at, password)   values('[email protected]',now(),'hashed_password')   returning id ), role_assignment as(   insert into users_roles(user_id, role_id)   select new_user.id, 10 from new_user   returning user_id ), log_entry as(   insert into user_logs(user_id, entry)   select role_assignment.user_id, 'Added to system' from role_assigment   returning user_id ) --return the new record with the assigned role for niceties select users.email, users.created_at, roles.name from users  inner join users_roles on users.id = users_roles.user_id inner join roles on roles.id = users_roles.role_id where users.id=(select user_id from log_entry); 

This example shows a killer feature of CTEs – they’re chainable. I’ve also wrapped everything inside a BEGIN and COMMIT so if there’s an error here for whatever reason, nothing gets written.

At the end there I’m returning the new users record along with the role assignment -but you can return whatever your application needs data-wise. This is why we SQL people!

Of course there are different ways of doing this (functions come to mind) but sometimes the syntactic niceties of Postgres make writing queries like this quite fun.

March 17, 2022

Importing data into PostgreSQL can be time consuming and painful – unless you toss the GUI tools and use scripts.

Importing a CSV Into PostgreSQL Like a Pro

April 17, 2020

This is part 3 of a series of posts I’m writing for Friendo, a web person who wants to get their hands a lot dirtier with Node and Postgres.

Postgres For Those Who Can’t Even, Part 3 – In The Real World

February 5, 2020

Getting up to speed with Postgres and Node can be daunting but in this post I’ll dive into how you can easily work with both – including JSON document storage… in Postgres!

Postgres For Those Who Can’t Even, Part 2 – Working with Node and JSON