The Basics - PostgreSQL: Up and Running - page 5

schema
Schemas are part of the ANSI SQL standard. They are the immediate next level of
organization within each database. If you think of the database as a country, schemas
would be the individual states (or provinces, prefectures, or departments, depend‐
ing on the country.) Most database objects first belong in a schema, which belongs
in a database. PostgreSQL automatically creates a schema named public when you
create a new database. PostgreSQL puts everything you create into public by default
unless you change the search_path of the database (discussed in an upcoming
item). If you have just a few tables, this is fine. But if you have thousands of tables,
you'll need to put them in different schemas.
catalog
Catalogs are system schemas that store PostgreSQL built-in functions and meta-
data. Each database is born containing two catalogs: pg_catalog , which has all the
functions, tables, system views, casts, and types packaged with PostgreSQL; and
information_schema , which consists of ANSI standard views that expose Post‐
greSQL metainformation in a format dictated by the ANSI SQL standard.
PostgreSQL practices what it preaches. You will find that PostgreSQL itself is built
atop a self-replicating structure. All settings to fine-tune servers are kept in system
tables that you're free to query and modify. This gives PostgreSQL a level of flexi‐
bility (or hackability) impossible to attain by proprietary database products. Go
ahead and take a close look inside the pg_catalog schema. You'll get a sense of how
PostgreSQL is put together. If you have superuser privileges, you have the right to
make updates to the schema directly (and to screw up your installation royally).
The information_schema catalog is one you'll also find in MySQL and SQL Server.
The most commonly used views in the PostgreSQL information_schema are col
umns , which lists all table columns in a database; tables , which lists all tables (in‐
cluding views) in a database; and views , which lists all views and the associated SQL
to build rebuild the view. Again, you will also find these views in MySQL and SQL
Server, with a subset of columns that PostgreSQL has. PostgreSQL adds a couple
more columns, such as columns.udt_name, to describe custom data type columns.
Although columns , tables , and views are all implemented as PostgreSQL views,
pgAdmin shows them in an information_schema→Catalog Objects branch.
variable
Part of what PostgreSQL calls the Grand Unified Configuration (GUC), variables
are various options that can be set at the service level, database level, and other
levels. One option that trips up a lot of people is search_path , which controls which
schema assets don't need to be prefixed with the schema name to be used. We discuss
search_path in greater detail in “Using Schemas” on page 27 .