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.
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
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 ...