Name: Delete Postgres Records
Uploaded: 2017-01-11T19:27:53-06:00
Description: 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.

Create a Postgres Table

Insert Data into Postgres Tables

Filter Data in a Postgres Table with Query Statements

Update Data in Postgres

Delete Postgres Records

Group and Aggregate Data in Postgres

Sort Postgres Tables

Ensure Uniqueness in Postgres

Use Foreign Keys to Ensure Data Integrity in Postgres

Create Foreign Keys Across Multiple Fields in Postgres

Enforce Custom Logic with Check Constraints in Postgres

Speed Up Postgres Queries with Indexes

Find Intersecting Data with Postgres’ Inner Join

Select Distinct Data in Postgres

Get Started With PostgreSQL

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.

https://app.egghead.io/api/v1/lessons/postgresql-delete-postgres-records/subtitles

[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.

Delete Postgres Records

Share this video with your friends

Course

Get Started With PostgreSQL