Database Reference
In-Depth Information
extension
Introduced in PostgreSQL 9.1, this feature allows developers to package functions,
data types, casts, custom index types, tables, GUCs, etc. for installation or removal
as a unit. Extensions are similar in concept to Oracle packages and are the preferred
method for distributing add-ons. You should follow the developer's instructions on
how to install the extension files onto your server. This usually involves installing
the extension binaries and scripts. Once done, you must enable the extension for
each database separately.
You don't need to enable every extension you use in all databases. For example, if
you need advanced text search in only one of your databases, enable
fuzzystr
match
just for that database. When you add extensions, you have a choice of the
schemas they will go in. If you take the default, extension objects will litter the
public
schema. This could make that schema unwieldy, especially if you store your
own database objects in there. We recommend that you create a separate schema
that will house all extensions and even create a separate schema to hold each large
extension. Include the new schemas in the
search_path
variable of the database so
you can use the functions without specifying which schema they're in. Some ex‐
tensions dictate which schema they should be installed in. For those, you won't be
able to change the schema. For example, many language extensions, such as plv8,
must be installed in
pg_catalog
.
table
Tables are the workhorses of any database. In PostgreSQL, tables are first of all
citizens of their respective schemas, before being citizens of the database.
PostgreSQL tables have two remarkable talents. First, they recognize parents and
children. This hierarchy streamlines your database design and can save you endless
lines of looping code when querying similar tables. We cover inheritance in
Example 6-2
.
Second, creating a table automatically results in the creation of an accompanying
custom data type. In other words, you can define a complete data structure as a
table and then use it as a column in another table. See
“Custom and Composite
Data Types” on page 103
for a thorough discussion of composite types.
foreign table
and
foreign data wrapper
Foreign tables
showed their faces in version 9.1. These are virtual tables linked to
data outside a PostgreSQL database. Once you've configured the link, you can query
them like any other tables. Foreign tables can link to CSV files, a PostgreSQL table
on another server, a table in a different product such as SQL Server or Oracle, a
NoSQL database such as Redis, or even a web service such as Twitter or Salesforce.
Configuring foreign tables is done through
foreign data wrappers
(FDWs). FDWs
contain the magic handshake between PostgreSQL and external data sources. Their
