PostgreSQL: Up and Running

Chapter 7. SQL: The PostgreSQL Way

PostgreSQL is one of the most ANSI-SQL compliant databases on the market. It even supports many of the additions introduced with the SQL:2006+ standard. PostgreSQL goes much further and adds constructs that range from mundane syntax shorthands to avant-garde features that break the bounds of traditional SQL. In this chapter, we’ll cover some SQL constructs not often found in other databases. For this chapter, you should have a working knowledge of SQL; otherwise, you may not appreciate the labor-saving tidbits that PostgreSQL brings to the table.

SQL Views

Like most relational databases, PostgreSQL supports views. Some things have changed over the years on how views work and how you can update the underlying tables via updates on views. In pre-PostgreSQL 9.1, views were updatable but required INSTEAD OF UPDATE, DELETE rules on the view. In PostgreSQL 9.1, the preferred way of updating data via a view is to use INSTEAD OF triggers instead of rules, though rules are still supported. The trigger approach is standards compliant and more along the lines of what you’ll find in other databases that support triggers and updatable views.

Unlike Microsoft SQL Server and MySQL, simple views are not automatically updatable and require writing an instead-of rule or trigger to make them updatable. On the plus side, you have great control over how the underlying tables will be updated. We’ll cover triggers in more detail in Chapter 8. You can see an example of building ...