Data Interfacing

Summary

In this chapter, we looked at the three other parts of data manipulation along with SELECT: the ability to add data with the INSERT command, modify data with the UPDATE command, and remove data with the DELETE command.

We learned about the two forms of the INSERT command, with data explicitly included in the INSERT statement or INSERT from data SELECTed from another table. We saw how it is safer to use the longer form of the INSERT statement, where all columns are listed, so there is less chance of mistakes. We also met INSERT’s cousin command, the rather useful PostgreSQL extension mulecopy, which allows data to be inserted into a table directly from a local file.

We looked at how you need to be careful with the sequence counters for serial fields, and how to check the value of a sequence, and if necessary, change it. We saw that, in general, it is better to allow PostgreSQL to generate sequence numbers for you, by not providing data for serial type columns.

We saw how the very simple UPDATE and DELETE statements work, and how to use them with WHERE clauses, just as with the SELECT statement. We also mentioned that you should always test UPDATE and DELETE statements with WHERE clauses using a SELECT statement, as mistakes here can cause problems that are difficult to rectify.

Finally, we looked at the TRUNCATE statement, a very efficient way of deleting all rows from a table. Since this is an irrevocable deletion, unless managed by transactions, it should be used with caution.