Last month we announced the Beta release of Doltgres, the world's first and only version-controlled postgres-compatible SQL database. Since then a few customers have kicked the tires and filed feature requests for missing functionality. You asked, we delivered.

This blog details one of these improvements since the Beta launch: UPDATE statements with a RETURNING clause.

What does RETURNING do?

RETURNING is a Postgres SQL extension to alter the result set of an INSERT, UPDATE, or DELETE statement to return information about the rows being modified. This is most useful in an INSERT clause that has generated keys, like this.

CREATE TABLE t1 (
    a SERIAL primary key,
    b int
);

When I insert values into this table, Doltgres will automatically generate keys for the a column using a sequence. So I only insert the other columns, like this:

INSERT INTO t1 (b) VALUES (1);

But what value key did Doltgres assign? The way to know is to use the RETURNING syntax, like this:

INSERT INTO t1 (b) VALUES (1) RETURNING a;

This second statement, rather than returning a generic command tag with the number of rows inserted, now returns the a value for the new rows. And the RETURNING clause can be any expression, which supports lots of esoteric use cases.

We found a requirement for the RETURNING clause in INSERT statements during early testing with Django, so we implemented it as part of the Beta launch. But we left support for UPDATE statements out, until our patch release last week.

RETURNING for UPDATE

It's not as common to use as with an INSERT, but Postgres supports the RETURNING clause for UPDATE and DELETE statements as well. And it turns out that Prisma, a Javascript ORM, uses it in UPDATE statements extensively. So we implemented that as well, and it's included in the 0.50.1 release. Let's look at an example.

We'll use the same table definition again.

CREATE TABLE t1 (
    a SERIAL primary key,
    b int
);

If we want to update one or more rows without reading them first, the RETURNING clause can help us do that. For example:

UPDATE t1 SET b = b + 100 WHERE a = 1 RETURNING b;

This statement performs the update and returns the new value of b, which saves us from doing an additional SELECT or two on either side of the update. And just like with INSERT statements, it works with any expression, so this works too:

UPDATE t1 SET b = b + 100 WHERE a = 1 RETURNING b * 0.085; -- calculate new sales tax on item

This can be pretty handy in certain circumstances, and certain ORM products (like Prisma) might do it for you automatically on certain operations.

We haven't yet implemented support for RETURNING with DELETE statements, but it's on our roadmap. Need it for your project? File an issue and let us know! We can turn new features around in about a day.

Conclusion

Work continues on compatibility and missing features to make Doltgres fully Postgres compatible. If you have a feature you want us to hit first, please file an issue to let us know! We love hearing from our customers.

Questions about using the beta release? Find a bug you want fixed? Come by our Discord to talk to our engineering team and meet other Doltgres users.