How do you get the ID of the row created by an INSERT using Postgres?
RETURNINGclause on your insert queries. (It turns out that
RETURNINGworks for updates, too, but that’s not what we’re concentrating on at the moment. We’re interested in getting the IDs of
catalogtable, with four columns:
- product ID (
product_id, the primary key of the table)
- name (
- stock keeping unit aka SKU (
- price (
product_id) of the new row looks like this:
insert into "catalog" ("name", "sku", "price") values ('foo', 'BAR', 34.89) returning "product_id"
returningat 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 RacketUsing Ryan Culpepper’s excellent
dbmodule, you can build queries that use the
RETURNINGclause. (NB: The
dbmodule is built in to Racket. No need for a separate download.) Specifically, the
query-valuefunction 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
catalogtable, as follows:
Here, we are imagining that we are working with a product and that we have the name, SKU, and price.
(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))
The connectionIn 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 placeholdersIn the example, the query that gets executed,
uses a technique called placeholders. That’s what the
insert into "catalog" ("name", "sku", "price") values ($1, $2, $3) returning "product_id"
$3are. The values
pricewill 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:
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
(format "insert into \"catalog\" (\"name\", \"sku\", \"price\") values ('~a', '~a', ~a)" name sku price) returning "product_id"
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.