In Part 2, we learned how to create tables in Postgres. We also looked at how to connect to the Database, so see part 2 if you need a refresher. For this part, we are going to look at how to insert, update, and then delete. Once we have learned to do all three, our Database will be ready for any API or frontend we may want to create later down the road.
As we noticed from the last part, Postgresql is very similar in syntax to MySQL. As we learn more today, I found those similarities continue, with a few differences. Overall, if you know MySQL the transition to Postgresql will be much simpler than other SQL transitions. So, knowing what our goal is, let’s jump right into the code.
Inserting
As an obvious note, you will need to start up Postgresql, jump into its interface, then connect to the database. If you are having any issues, you can review the previous parts of this tutorial to find how to start the Postgres service and how to log in. Once in, we need to connect to the database. We already learned this in Part 2, but as a brief refresher you can use the following command:
c recipesNext, we can look at our tables. Again, we covered this in Part 2, but if you haven’t viewed your database in a while, it would be a good idea to review what tables you created before trying to insert data.
dtFor my Database, the first table we will add to the MealType table. To insert, we specify what columns we will be inserting into, and then list the values:
NSERT INTO MealType(MealName) VALUES ('Breakfast');
To ensure the data is there, we can simply select all from the table:
SELECT * FROM MealType;
We will need a few more records in the table before we can start using the data. However, with four more records to add in, creating multiple INSERT statements could get tedious. With Postgres, however, we can simply comma separate a list of values to insert multiple records at a time:
INSERT INTO MealType(MealName) VALUES ('Lunch'),
('Appetizer'),
('Entree'),
('Dessert');
And select one more time to make sure that the Serial value for the Primary Key is working correctly.
Checking our tables, we will want to add to the pantry table next. However, what if we forget the column names? In MySQL, we would use a DESCRIBE statement. Postgresql also has one of these statements using the following command with your table name:
d pantry;
Now that we know what the columns are, and what’s required, we can start adding in more data. We can add multiple records at a time again but will only add the pantry items we need to create our first recipe.
As a quick note, I made a syntax error while writing out these next lines. It was a missing comma. Usually, there’s a generic syntax error statement. However, Postgresql impressed me with this error message:
As you can see, it’s very specific and even points out the issue. Once fixed, we can add in our records:
INSERT INTO Pantry(Title, TotalAmt, TotalAmtUnit) VALUES ('Flour', 5, 'LBS'),
('Butter', 1, 'LBS'),
('Milk', 1, 'GAL'),
('Salt', 1, 'LBS'),
('Sugar', 5, 'LBS'),
('Baking Powder', 5, 'LBS'),
('Egg', 12, 'Egg');
The next table I will be adding to is the Recipe table, which has a Foreign key from the MealType table. We will need to look at the MealType records to determine the ID for our Foreign Key. In this case, the recipe we are making is for breakfast, so we will need ID 1, which is "Breakfast". Next, we are ready to insert our values.
INSERT INTO Recipe(MealTypeID, Title) VALUES (1, 'Pancakes');
Next, I will be adding to my instructions table. This will have a Foreign Key from the Recipe table. Because we have only added one recipe to the table, we know that the ID must be 1.
INSERT INTO Instructions(RecipeID, StepNum, Description) VALUES (1, 1, 'Melt butter and allow to cool. Add milk and one egg. Mix thoroughly.'),
(1, 2, 'Mix dry ingredient together in a separate bowl.'),
(1, 3, 'Preheat non-stick pan to medium, or use no-stick spray on pan.'),
(1, 4, 'Pour wet ingredients into dry ingredients. Mix until just combined. Do not overmix.')
(1, 5, 'Pour ¼ cup fulls of pancake batter into heated pan. Flip once bubbles pop or until golden brown. Serve hot.');
You can see I’ve already made typos, but that’s fine. Those can be corrected once we start updating records. For now, I’ve got just one more table left. However, that table is an Associative table, which means it connects at least two of our tables. In this case, the Ingredients table connects both Recipe and Pantry. For that reason, we will need to view both to find what our Foreign Key values should be. Again, we can assume Recipe will be 1 as there are no other records in that table. But for Pantry, we will need to view the records so we can associate which keys will be needed for each ingredient.
Updating
Once your records have been inserted, we can look at how to update records. When setting up the Instructions, I used "ingredient" instead of "ingredients". However, that’s an easy fix. We can use a simple UPDATE to change the text. In our update, we will first declare the table, then SET the value to our desired column, finally, also add a WHERE clause to ensure we do not update all records in the table. The syntax for this statement is like what you would find in MySQL.
UPDATE Instructions
SET Description = 'Mix dry ingredients together in a separate bowl.'
WHERE InstructionID = 2;
To check our changes, we can add the WHERE clause onto a SELECT statement:
SELECT *
FROM Instructions
WHERE InstructionID = 2;
For updates, like any other SQL, you do not have to use the Primary Key to update. You could update everything inside a table by simply leaving off the WHERE clause. We don’t be doing that, as we would have to change multiple fields back. However, we can use any other field in the Ingredient table. If you remember, I set both "tsp" and "tbsp" to lower case for our MeasurementUnit in the Ingredient table. But what if I only want to replace the single "t" with a capital "T"? For that, we can just use the REPLACE function. Note, I know that I don’t have any other t’s as a value, so it will be safe to update. You may want to also define the ID if you have other records that may be changed because of it.
UPDATE Ingredient
SET MeasurementUnit = REPLACE(MeasurementUnit, 't', 'T');
The number "7" had me worried. I would assume it should only be "3", as that is the number of records that should have been updated. So I viewed all records and it seems all are being displayed exactly as expected:
Deleting
First, I will add a random entry to MealType so that we can delete it. It will be a duplicate:
As you can see, there are two "Breakfast" entries. Because that is a duplicate, although it would not be considered one to the Database while there are separate IDs, we will want to remove it. Again, the syntax will be like MySQL. First, start the DELETE and select which table, then use the WHERE clause to determine which record to remove:
DELETE FROM MealType
WHERE MealTypeID = 6;
When you delete, the SERIAL ID will not reset. This means the next record you insert will be "7", even though there will only be 6 records in the table. That is fine, as the ID is only for uniqueness.
For the next test for deleting, I want to dump all records from a table. However, it will be a parent table. Now, this delete should fail because there are child records attached to the parent records. We did not specify that CASCADE should be on, so it shouldn’t automatically cascade. However, better safe than sorry. To make sure we don’t lose all of our work, we’ll perform our first database dump. I’ve done this in a previous article, but because that wasn’t Postgres specific, we will take a look again. Using our Postgres user again, we will perform a pg_dump. Also, please note this will not take place inside the Postgresql terminal, but your regular bash terminal.
sudo -u postgres pg_dump recipes > recipes.sql
Now we can try deleting from the recipe table:
DELETE FROM Recipe;
Looks like we got an error because CASCADE is not automatically on. If CASCADE were on, it would have deleted all child records associated with the parent. But for now, we have learned about Inserting, Updating, and Deleting enough for you to have a good start on your personal Postgresql database.
Conclusion
In this part, we talked about how to insert, update, and delete records from our Postgresql database. While inserting, we learned that we could add multiple records with only a single statement. With updating, we learned that not only could you change an entire value, but you can also use functions such as REPLACE right in the SET statement. While deleting, we learned that although it is always best to air on the side of caution, tables will not automatically be set to CASCADE on UPDATE or DELETE events. Instead, the user can select when and what will CASCADE. Without it being automatic, we are saved from accidentally wiping out all our recipes, ingredients, and instructions at the same time.
For now, this will be all we work on for the actual database side. In the future, I may return to add a front end. Before that, I will need to build out an API for this. It will likely be using FastAPI. But until then, I hope this tutorial has been educational and enjoyable. Cheers!
Read all my articles for free with my weekly newsletter, thanks!
Want to read all articles on Medium? Become a Medium member today!
Check out my recent articles:
Open-Source Reporting Tools Worth A Look
Middleware in FastAPI— What is it?
References:
PostgreSQL INSERT Multiple Rows
PostgreSQL UPDATE substring replacement
PostgreSQL – How To Dump Database to SQL Script – pg_dump Utility
