Count, Sort, Update, and Delete

[00:00:00]
>> Let's say we wanna delete every user right now that has never logged in. Where you can do that is you can say WHERE last_login IS NULL, like that, and that'll be a lot, so it's just for the moment, say LIMIT 10. And then here you can see we've grabbed out of here ten people who have never logged in, that's, How you do that with that is null.

[00:00:31]
What you can also do is not null, right? This will also work, this will give you the people that were, you can see that has no overlap. So that's how you kind of do that filtering, right? You can say where, what if we wanted to do something a bit more complex?

[00:00:50]
Like we wanted to filter on multiple different things, well, you can do and in the same way. So let's say we wanted to do it where people haven't logged in and their accounts are more than six months old, right? So let's say we're cleaning out the database, we wanna clean out anyone that hasn't logged in, and their account is more than six months old.

[00:01:11]
So we'll do the same thing where last login is null and then you just say AND, and now you can put in another where clause. So I'm gonna say now And then you can actually do date math directly, and Postgres is pretty smart and can figure that out.

[00:01:30]
So I'm gonna say NOW() -interval Six months, it's smart enough to turn that into date math for you, and then we can just say LIMIT 10. Yeah, so we have to say created_on, and created_on is less than the interval now minus 6 months. All right, so these people haven't logged in and their accounts are more than six months old, it's the first ten of them.

[00:02:17]
So again, let's break that down. You're selecting these fields from users where last login is null. So these people have never logged in and created on is less than, oops, is less than now which is the current timestamp at this moment in time minus interval of six months, right?

[00:02:42]
So that time space, and then you're limited that to ten. What happens if we want to find the oldest user account? We gonna say SELECT user_id, email, created_on, FROM users ORDER BY created_on, and we'll do LIMIT 10 again. Now I think all of these accounts are within a year of each other or something like that.

[00:03:23]
So yeah, a year ago which would be in 2019, the forever times ago. These would be our oldest user accounts. Now, if you wanted to find the newest ones, it's pretty easy, all you have to do is put created_on, ORDER BY created_on DESC for descending. And you can see we have stuff that was created yesterday.

[00:03:54]
So that's order by, order by just lets you sort, right? That's the sorting command here. And just so you know, we put DESC here, you can also put a ASC for ascending, it's just implied, right? So if you leave it out, it's automatically assumed that you mean ascending.

[00:04:14]
Let's go into count, this is a good one. What if we just wanna know how many users we have? SELECT COUNT(*) like that from users, we have 1000 users because that's exactly how many I put in the database. The star represents just we're looking for total roles, in general we're ignoring everything else.

[00:04:40]
If you wanna look at how many users have ever logged in, since count will ignore null values, what you can do is count the amount of last logins. And you can see here 678 users have a last login, that means the other ones have never logged in. So that's just what that star means, it just means count the ones where this is not know, or if you put stars, it says count all the rows.

[00:05:08]
So I'm gonna say UPDATE users SET last_login=NOW(), so this is a query that we would run, for example, if a user logged in, WHERE user_id=1. And so we could run this right now just like that, nd it would work, right? This would just work as is, this would update that user.

[00:05:35]
Let's say we wanted to update it and also get that record back, what you can do is do RETURNING *, right? This is basically saying, do you remember the find one and update that we did in MongoDB, this is the same behavior in Postgres. This returning star just says, also give me back that thing.

[00:05:59]
So you can see here, user ID one now does have a last login whereas before they didn't. Or if we did it again, you'll notice that it's different because time has advanced since we last ran that command. And just to prove my point here, if you just run this with a semicolon there, it will just tell you I updated one record, and it won't tell you what it was.

[00:06:22]
So that's what that returning, you can always just tack it on the end, give me back the stuff that I updated. If you wanna update multiple things, the way that looks is UPDATE users SET full_name='Brian Holt', email='lol@example.com'. WHERE user_id=2 RETUNING *. I put an extra comment there, I should not do that, and you can see there now I've updated User ID two.

[00:07:12]
So now that their full name is Brian Holt, and their email is lol@exmaple. [LAUGH] And let's do one more here. We'll say DELETE FROM users WHERE user_id=1000. And there you go, that's how you delete a record.
>> I just have a question, returning star means returning all these fields?

[00:07:43]
>> Returning star means I want the record back that I updated, right? So in this particular case, I updated user_id2, please return to me that whole record that I just updated after it's been updated. So you can see here, I set full name you called the Brian Holt, that's what this did.

[00:08:02]
Whereas up here, when I didn't pass it in, it just says UPDATE 1, it doesn't tell you what it updated, it just goes like yep, update done. Answer Stevens question, how was samples Postgres SQL imported. I literally just copied the contents of that file and pasted it into PSQL.

[00:08:26]
There's another way of doing it, but it got complicated with containers being in the way, so the easiest way is to just copy and paste it. It will take a while. Is there any way to get lost records? Let me infer what you mean by lost, that you mean deleted, and if that's not correct, please let me know.

[00:08:50]
The answer is no, it's kind of like one of those like, if you RM a file, its real gone, it's not coming back, you should be very cautious of what you delete. Case in point, very first job, aka my internship, I dropped an entire table of telemetry data that we never got back.

[00:09:07]
I'm pretty sure whoever would have done that would have gotten fired, but I was an intern, it was my second day, and they didn't wanna fire me. I mean, hopefully you have a company that's smart enough to run periodic backups, so that you're not losing much more than like a half day's worth of stuff.

[00:09:22]
In my case, it was real gone, we did not have any back ons, it was for real gone, and it was months of data center health data. So they were pretty upset, but hopefully, an e-commerce company backs up their stuff, [LAUGH] that's really sad.
>> The other strategy I've seen people employ is soft deleting, where they change a deleted to one.

[00:09:46]
>> I've seen it too, so they'll do is they'll excise something from a table, so they'll remove it from one table and put it into like a deleted records table. And then that table will just be cleaned out every 30 days or something like that. There's a lot of strategies you can do to mitigate it.

[00:10:00]
But as soon as I say something like this, where it's you say delete where user equals 1000, it's gone, this record is not retrievable at this point. I mean, I guess you could probably shut down the computer, pull it out, and try and read the magnetic data back, but you're probably not.

[00:10:18]
Probably not gonna work.