Join Bill Weinman for an in-depth discussion in this video Storing and deleting data with INSERT, UPDATE, and DELETE, part of PostgreSQL 9 with PHP Essential Training.
Storing and deleting data in Postgres is pretty much like it is in other SQL relational databases. Let's take a look at the basics. Here we're creating a table with three columns, a, b, and c, and we're inserting values a, b, and c into five rows. We select those rows and then drop the table, and there is our results, we have the headings a, b, and c and the five rows of a, b, and c. So that very simply is how you store data in a table. You create the table with the appropriate column types and you insert those types of data into the respective columns in each row, row by row, using the INSERT statement.
Of course that's not the only way to get data into a table, you can also do something like this. Let me go ahead and write in here. Before we drop that table, I'm going to create a table b with columns d, e, and f and we're going to INSERT INTO b FROM a, and so that's how we do that using what's called a subselect to select everything from a and insert it right into b. Then we'll go ahead and SELECT * FROM b, so you can see that, and then we'll drop them both.
So there is table b and it has got columns d, e, and f, and you'll notice it has exactly the same data from a. But wait, there's more. We can also do something like this. INSERT INTO b (f, e, d) SELECT * FROM a. So we're turning the order around. Instead of d, e, f, it's f, e, d, and yet we're selecting * FROM a, so that's going to be a, b, c, and so we'll have five more rows with everything reversed.
I'll go ahead and run that. And when we scroll down here, you'll see the first five rows are a, b, c and the second five rows are c, b, a. In fact, we can do this even more convolutedly if we want to. If we reverse the order of both f, e, d and c, a, b, you'll find that it will come out in the right order. And so we'll go again, and now the first five rows in our second table are a, b, c, the second five rows are c, b, a, and the third five rows are b, a, c, because we did it in this order c, a, b from a.
So as you can see, there is number of different ways you can insert data into a table, including copying that data from another table with subselects. Now let's take a look at how you update data that's already in a table. We'll go ahead and we'll copy this UPDATE example from our examples file in Chapter 04, Command+C to copy it, and then going back to the browser and select all of this and paste it in, and we'll take a look at how this works. First, we're creating a table t with the SERIAL PRIMARY KEY for the id and a quote and byline for TEXT.
And so we're just going to be inserting the quote and the byline, and we've got Aye Carumba! from Bart Simpson and a few other interesting quotes, and then we're going to select from that. And why don't I go ahead and take out these other lines for now, and we'll just run this much of it so we can see that that works. And there we have four different rows in this table with quotes and bylines. Now, if we want to change one, we can do it like this. We update the table, UPDATE t SET quote = and change it WHERE id=4.
So for the fourth row instead of saying "I'll be back" The Terminator says "Hasta la vista, baby." and we select Go. And you see, we have the first query. It's I'll be back, and the second query it's Hasta la vista, baby. So we updated that row and you notice we have this WHERE clause. If we did not have that WHERE clause, well, why don't I go ahead and take it out, and you can see what happens. It will change all of the quotes in the entire table. And if I take out the WHERE clause in the SELECT here, then you'll be able to see the whole table, And you can see that they all say Hasta la vista, baby.
But if I put in that WHERE clause here, id=4, then you'll see that just that last one, just number 4 is the one that gets updated. So that WHERE clause is really important when you're doing update. It's actually pretty easy to accidentally overwrite an entire table in a database. If you like, you can update more than one column at a time in a row, and that looks like this.
And now that fourth row, instead of having a quote from The Terminator, it has got Rosebud from Charles Foster Kane. And then finally, if you want to delete a row, it's as simple as changing this UPDATE to saying DELETE FROM t WHERE id =4, and then it will simply delete the row entirely, and there, now that row is gone. So that in a nutshell is just a quick overview of how you insert data into a table, how you update data in a table, and how you delete data from a table.
- Installing PostgreSQL
- Understanding database architecture
- Inserting, updating, and deleting data in a table
- Creating a database library
- Indexing ID fields
- Storing numbers, text, and Boolean values
- Reading data
- Using casts to force type
- Using mathematical functions
- Concatenating strings
- Working with date and time functions and operators
- Defining CRUD
- Using PHP to insert, read, update, and delete rows in a database