3. Lesson: Adding Data to the Model¶

3.1. Insert statement¶

How do you add data to a table? The sql INSERT statement provides the functionality for this:

insert into streets (name) values ('High street');

A couple of things to note:

• After the table name (streets), you list the column names that you will be populating (in this case only the name column).
• After the values keyword, place the list of field values.
• Strings should be quoted using single quotes.
• Note that we did not insert a value for the id column; this is because it is a sequence and will be auto-generated.
• If you do manually set the id, you may cause serious problems with the integrity of your database.

You should see INSERT 0 1 if it is successful.

You can see the result of your insert action by selecting all the data in the table:

select * from streets;

Result:

select * from streets;
 id |    name
----+-------------
  1 | High street
(1 row)

3.2. Sequencing Data Addition According to Constraints¶

3.3. Try Yourself ¶

Try to add a person object to the people table with the following details:

Name: Joe Smith
House Number: 55
Street: Main Street
Phone: 072 882 33 21

At this point, you should have an error report if you try to do this without first creating a record for Main Street in the streets table.

You should have also noticed that:

• You can’t add the street using its name
• You can’t add a street using a street id before first creating the street record on the streets table

Remember that our two tables are linked via a Primary/Foreign Key pair. This means that no valid person can be created without there also being a valid corresponding street record.

Using the above knowledge, add the new person to the database.

Check your results

3.4. Select data¶

We have already shown you the syntax for selecting records. Let’s look at a few more examples:

select name from streets;

select * from streets;

select * from streets where name='Main Road';

In later sessions we will go into more detail on how to select and filter data.

3.5. Update data¶

What if you want to make a change to some existing data? For example, a street name is changed:

update streets set name='New Main Road' where name='Main Road';

Be very careful using such update statements - if more than one record matches your WHERE clause, they will all be updated!

A better solution is to use the primary key of the table to reference the record to be changed:

update streets set name='New Main Road' where id=2;

It should return UPDATE 1.

3.6. Delete Data¶

In order to delete an object from a table, use the DELETE command:

delete from people where name = 'Joe Smith';

Let’s look at our people table now:

address=# select * from people;

  id | name | house_no | street_id | phone_no
 ----+------+----------+-----------+----------
(0 rows)

3.7. Try Yourself ¶

Use the skills you have learned to add some new friends to your database:

      name       | house_no | street_id |   phone_no
-----------------+----------+-----------+--------------
Joe Bloggs       |        3 |         2 | 072 887 23 45
Jane Smith       |       55 |         3 | 072 837 33 35
Roger Jones      |       33 |         1 | 072 832 31 38
Sally Norman     |       83 |         1 | 072 932 31 32

3.8. In Conclusion¶

Now you know how to add new data to the existing models you created previously. Remember that if you want to add new kinds of data, you may want to modify and/or create new models to contain that data.

3.9. What’s Next?¶

Now that you’ve added some data, you’ll learn how to use queries to access this data in various ways.