How do you get the ID of the row created by an INSERT using Postgres?

When you save data to a relational database such as Postgres, MySQL, and kin, how do you make sure you get the ID of a row that you just inserted?

The answer, in short, is that it depends on the system you’re using. There seems to be (for now) no universal approach. Here’s how you can get the information you need with Postgres. (There may even be multiple ways of getting the ID in Postgres; what follows is but one approach.)

The answer (in Postgres) is that you can use a RETURNING clause on your insert queries. (It turns out that RETURNING works for updates, too, but that’s not what we’re concentrating on at the moment. We’re interested in getting the IDs of newly created rows.)

To illustrate, imagine we are maintaining a catalog, stored in a catalog table, with four columns:

(There may very well be columns other than these. You can adapt the example to your needs.)

A query that adds a new row to the database and, after insertion, returns the ID (that is to say, the product_id) of the new row looks like this:

1
2
3
insert into "catalog" ("name", "sku", "price")
values ('foo', 'BAR', 34.89)
returning "product_id"

The returning at the end is a nice add-on that allows us to get the ID of the newly added row. Using this feature, one can ask Postgres to return essentially any value you want; returning the ID of the newly inserted row is just the tip of the iceberg.

Usage in Racket

Using Ryan Culpepper’s excellent db module, you can build queries that use the RETURNING clause. (NB: The db module is built in to Racket. No need for a separate download.)

Specifically, the query-value function is handy when inserting new rows into a table. We fire off our INSERT query, with RETURNING clause, and get the ID back as a Racket value.

Continuing the example above, we can now write a function, insert-new-product!, that adds records using (suitable) Racket values to the catalog table, as follows:

1
2
3
4
5
6
7
8
9
(define (insert-new-product! name sku price)
  (query-value
    the-db
    "insert into \"catalog\" (\"name\", \"sku\", \"price\")
values ($1, $2, $3)
returning \"product_id\"" name sku price))

Here, we are imagining that we are working with a product and that we have the name, SKU, and price.

The connection

In the previous example, I’ve assumed that a database connection has been set up and is available under the-db. That can be achieved using postgresql-connect. An even smarter way to set up your connections is to use a virtual database connection pool. (Yes, it is as cool as it sounds.)

Side topic: Query placeholders

In the example, the query that gets executed,

1
2
3
insert into "catalog" ("name", "sku", "price")
values ($1, $2, $3)
returning "product_id"

uses a technique called placeholders. That’s what the $1, $2, and $3 are. The values name, sku, and price will be inserted—in that order—in a way appropriate for Postgres.

Using placeholders is generally preferable to building the query using string-building functions such as format. Doing things that way would look something like this:

1
2
3
4
5
6
(format "insert into \"catalog\" (\"name\", \"sku\", \"price\")
values ('~a', '~a', ~a)" name sku price)
returning "product_id"

That string could be sent off to Postgres. It may even do what you want most of the time.

But notice that things are starting to get precarious. Do you see that we used single quotes for name and sku, but not for price? That kind of fussiness is rather fragile and is bound to bite you in the behind sooner or later.

In any case, for security reasons, building queries as strings, with Racket values thrown, is generally something to avoid.

The difficulty is that you don’t know what kind of values you’re plugging into the string. If you’re building a system that builds SQL queries using values supplied from your users, you’ll definitely want to use placeholders rather than building queries-as-strings by yourself.