Appendix C. SQL primer

 

PostgreSQL supports almost the whole ANSI SQL 92, 1999 standard logic, as well as many of the SQL:2003, SQL:2006, SQL:2008 constructs, and some of the SQL:2011 constructs. In this appendix, we’ll cover some of these as well as some PostgreSQL-specific SQL language extensions. Because we’ll remain fairly focused on standard functionality, the content in this appendix is also applicable to other standards-compliant relational databases.

C.1. information_schema

The information_schema is a catalog introduced in SQL 92 and enhanced in each subsequent version of the spec. Although it’s a standard, sadly most commercial and open source databases don’t completely support it. We know that the following common databases do: PostgreSQL (7.3+), MySQL 5+, and Microsoft SQL Server 2000+. Oracle and IBM do via user-supported contributions.

The most useful views in this schema are tables, columns, and views; they provide a catalog of all the tables, columns, and views in your database. To get a list of all non-system tables in PostgreSQL, you can run the following query (the information_schema.tables view in PostgreSQL will list only tables that you have access to):

SELECT table_schema, table_name, table_type
    FROM information_schema.tables
    WHERE table_schema NOT IN('pg_catalog', 'information_schema')
    ORDER BY table_schema, table_name;

C.2. Querying data with SQL

C.3. UPDATE, INSERT, and DELETE