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