Okay, we just finished upserts, which are very awesome. You gather up a query, you throw it over the wall and it either inserts or it updates, but if it updates, it'd be nice to kind of know what was the value, what's the new value, right?

Let's look at this upsert again. We're still here at hits:homepage and let's go ahead and just increment by one and we, we do our upsert, but the whole point of an upsert is like, let's do it all at once right? We're in this world where we have to do this insert and then or this upsert and then figure out, well what's the new value? Ah, it's 21. Great. I had to issue two queries. Well, that is not actually true. You can say returning. This is not just an upsert specific thing. We will talk about that in a second, but it is very helpful on upserts, you can say returning and in this case we're gonna say returning everything.

Just like a select *, return everything. Now look at that. We could just, you know, delete from kv, delete the whole table, and then do this and it's an insert, and we got the value of one back and now it's upsert, and we got the value of two back. Incredibly helpful. You don't have to do the whole thing, you could just do the value if you wanted. You could just do the key if you wanted. You could do key value, which in this case is the entire table. It's not that much different. But this is an incredibly powerful way to get back the values that were within Postgres's responsibility, right? We sent some stuff over, but we handed over the responsibility to Postgres to say, you figure out what to do with this, and after you figure it out, can you let me know? Can you give that back to me so that I am aware of it so that I don't have to turn around and issue another query?

This doesn't only work on upserts, it works on inserts, updates and deletes. A place that it can work on a delete here, as you can see, delete from kv, returning everything that was deleted. And that will show you all of the rows and columns that were deleted. That can be very, very helpful. Especially if you are doing delete from kv where expires_at is, yeah, is less than now or you know, current time or whatever. That might be very helpful to know like, "Hey, I'm gonna throw this query over the wall and say delete all this stuff." But I kind of wanna know, I kind of wanna know what was deleted without having to keep a diff myself and figure that out myself. This is also incredibly helpful when you're doing an insert and Postgres is in control of generating some of those columns. That could be of course a generated column. It could also be something where the default is like a current date or a current time, or just simply a primary key. Just simply a big end generated always as identity and you kind of want to insert it and then get that identity back. You can do that.

We're gonna do that here with insert into bookmarks and we'll insert the user ID and I believe it's called the URL values. I'm just gonna hope that a user ID of one exists and we'll say https, and then let's do tryhardstudios.com. It's a good website. If I insert that and I run that, it works. But what's the id? Maybe I wanted the id. If we do, again, if we do returning *, then I get the ID back, I get, well I get the whole row back and you'll see we still don't have that unique enforcement, so we can go back and delete duplicates using a CTE and a window function. But here I get the entire row back, which might be useful on the application side if I need to then, you know, potentially redirect them to a view page or an edit page for this resource. I need that unique ID so that I can show them that page after this has been created. Using returning * will give you back all of the values, including the ones that were in Postgres's control.