In the previous chapter we have covered the basics of using SQL to store and access your data in a PostgreSQL. We will now discuss some more advanced features of SQL that simplify the management and prevent loss or corruption of your data. Finally, we will look at some PostgreSQL extensions. This chapter will on occasion refer to examples found in to change or improve them, so it will be of advantage if you have read that chapter. Some examples from this chapter can also be found in advanced.sql in the tutorial directory. This file also contains some example data to load, which is not repeated here. (Refer to for how to use the file.) view Refer back to the queries in . Suppose the combined listing of weather records and city location is of particular interest to your application, but you don't want to type the query each time you need it. You can create a view over the query, which gives a name to the query that you can refer to like an ordinary table. CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate the details of the structure of your tables, which may change as your application evolves, behind consistent interfaces. Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon. foreign key referential integrity Recall the weather and the cities tables from . Consider the following problem: You want to make sure that no one can insert rows in the weather table that do not have a matching entry in the cities table. This is called maintaining the referential integrity of your data. In simplistic database systems this would be implemented (if at all) by first looking at the cities table to check if a matching record exists, and then inserting or rejecting the new weather records. This approach has a number of problems and is very inconvenient, so PostgreSQL can do this for you. The new declaration of the tables would look like this: CREATE TABLE cities ( name varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references weather, temp_lo int, temp_hi int, prcp real, date date ); Now try inserting an invalid record: INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28'); ERROR: <unnamed> referential integrity violation - key referenced from weather not found in cities The behavior of foreign keys can be finely tuned to your application. We will not go beyond this simple example in this tutorial and refer you to the Reference Manual for more information. Making correct use of foreign keys will definitely improve the quality of your database applications, so you are strongly encouraged to learn about them. This section needs to be written. inheritance Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design. Let's create two tables: A table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you're really clever you might invent some scheme like this: CREATE TABLE capitals ( name text, population real, altitude int, -- (in ft) state char(2) ); CREATE TABLE non_capitals ( name text, population real, altitude int -- (in ft) ); CREATE VIEW cities AS SELECT name, population, altitude FROM capitals UNION SELECT name, population, altitude FROM non_capitals; This works OK as far as querying goes, but it gets ugly when you need to update several rows, to name one thing. A better solution is this: CREATE TABLE cities ( name text, population real, altitude int -- (in ft) ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities); In this case, a row of capitals inherits all columns (name, population, and altitude) from its parent, cities. The type of the column name is text, a native Postgres type for variable length character strings. State capitals have an extra column, state, that shows their state. In PostgreSQL, a table can inherit from zero or more other tables. For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 ft.: SELECT name, altitude FROM cities WHERE altitude > 500; which returns: name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 Madison | 845 (3 rows) On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude of 500 ft. or higher: SELECT name, altitude FROM ONLY cities WHERE altitude > 500; name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 (2 rows) Here the ONLY before cities indicates that the query should be run over only the cities table, and not tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed -- SELECT, UPDATE and DELETE -- support this ONLY notation. PostgreSQL has many features not touched upon in this tutorial introduction, which has been oriented toward newer users of SQL. These features are discussed in more detail in both the User's Guide and the Programmer's Guide. If you feel you need more introductory material, please visit the PostgreSQL web site for links to more resources.