Delete Postgres Records

Brett Cassette
InstructorBrett Cassette
Share this video with your friends

Social Share Links

Send Tweet
Published 7 years ago
Updated 5 years ago

What if you no longer need some information? Perhaps your tables are very large and you need to clean up your table to speed it up. This lesson will teach us how to delete data from our database.

[00:00] Now we're going to talk about deleting data. I'm going to go ahead and paste in a bunch of inserts here for our movies and select all of our movies just so we can see that we have a bunch of "Kill Bills." 101 Kill Bills. I guess that's "101 Dalmatians" for Kill Bills.

[00:19] Just like with UPDATEs, with DELETEs, it's helpful to run the SELECT statement first. Let's select all from Movies where the count stars is greater than 90. Actually, let's just select the count of that so we know how many we're going to be deleting here.

[00:37] For each of these, I just wanted to limit the data and partition it easily, so I added a star for every single movie. We know we're going to delete 10. We know that less than 90 should be 90 movies. We know that the total count then will be 101.

[01:00] We're going to delete where they are greater than 90 and we're going to check back at this again. Remember, there are 10 of those movies. We should expect that 91 movies are left. We're going to delete from Movies where the count stars is greater than 90. That's 10 movies. Now we're down to 91.

[01:23] Let's remove this guy so where the count star is now greater than 70. That's 20, so we expect another 20 movies to be released. 71 movies should be left. That is, in fact, what we see.

[01:41] These are idempotent operations. We can keep running it. We still have exactly 71 stars. There's nothing new. The 71 movies, nothing new that we're going to be inserting in the meantime. Of course, we can do this with any column.

[01:58] We can say where the release date is less than 1984. See how many movies that is. That's another 50 movies. Where the release date is less than 1974. All right. We see that.

[02:25] Then finally, in the same way, that SELECT COUNT for movies tells us there are 31. If we delete from Movies with no WHERE clause, we're going to delete everything. We do that. We have a zero. That's DELETE.

egghead
egghead
~ 5 minutes ago

Member comments are a way for members to communicate, interact, and ask questions about a lesson.

The instructor or someone from the community might respond to your question Here are a few basic guidelines to commenting on egghead.io

Be on-Topic

Comments are for discussing a lesson. If you're having a general issue with the website functionality, please contact us at support@egghead.io.

Avoid meta-discussion

  • This was great!
  • This was horrible!
  • I didn't like this because it didn't match my skill level.
  • +1 It will likely be deleted as spam.

Code Problems?

Should be accompanied by code! Codesandbox or Stackblitz provide a way to share code and discuss it in context

Details and Context

Vague question? Vague answer. Any details and context you can provide will lure more interesting answers!

Markdown supported.
Become a member to join the discussionEnroll Today