The catalog pg_trigger stores triggers on tables and views. See CREATE TRIGGER for more information. Table 50-49. pg_trigger Columns Name Type References Description oid oid Row identifier (hidden attribute; must be explicitly selected) tgrelid oid pg_class.oid The table this trigger is on tgname name Trigger name (must be unique among triggers of same table) tgfoid oid pg_proc.oid The function to be called tgtype int2 Bit mask identifying trigger firing conditions tgenabled char Cont

There are two kinds of indexes that can be used to speed up full text searches. Note that indexes are not mandatory for full text searching, but in cases where a column is searched on a regular basis, an index is usually desirable. CREATE INDEX name ON table USING GIN (column); Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type. CREATE INDEX name ON table USING GIST (column); Creates a GiST (Generalized Search Tree)-based index. The column can be of

Traditionally, implementing a new index access method meant a lot of difficult work. It was necessary to understand the inner workings of the database, such as the lock manager and Write-Ahead Log. The GiST interface has a high level of abstraction, requiring the access method implementer only to implement the semantics of the data type being accessed. The GiST layer itself takes care of concurrency, logging and searching the tree structure. This extensibility should not be confused with the ex

Full Text Searching (or just text search) provides the capability to identify natural-language documents that satisfy a query, and optionally to sort them by relevance to the query. The most common type of search is to find all documents containing given query terms and return them in order of their similarity to the query. Notions of query and similarity are very flexible and depend on the specific application. The simplest search considers query as a set of words and similarity as the frequen

The following "parameters" are read-only, and are determined when PostgreSQL is compiled or when it is installed. As such, they have been excluded from the sample postgresql.conf file. These options report various aspects of PostgreSQL behavior that might be of interest to certain applications, particularly administrative front-ends. block_size (integer) Reports the size of a disk block. It is determined by the value of BLCKSZ when building the server. The default value is 8192 bytes. The me

Geometric data types represent two-dimensional spatial objects. Table 8-20 shows the geometric types available in PostgreSQL. Table 8-20. Geometric Types Name Storage Size Description Representation point 16 bytes Point on a plane (x,y) line 32 bytes Infinite line {A,B,C} lseg 32 bytes Finite line segment ((x1,y1),(x2,y2)) box 32 bytes Rectangular box ((x1,y1),(x2,y2)) path 16+16n bytes Closed path (similar to polygon) ((x1,y1),...) path 16+16n bytes Open path [(x1,y1),...] polygon 40+16n byt

Window functions provide the ability to perform calculations across sets of rows that are related to the current query row. See Section 3.5 for an introduction to this feature, and Section 4.2.8 for syntax details. The built-in window functions are listed in Table 9-56. Note that these functions must be invoked using window function syntax; that is an OVER clause is required. In addition to these functions, any built-in or user-defined normal aggregate function (but not ordered-set or hypotheti

PostgreSQL offers encryption at several levels, and provides flexibility in protecting data from disclosure due to database server theft, unscrupulous administrators, and insecure networks. Encryption might also be required to secure sensitive data such as medical records or financial transactions. Password Storage Encryption By default, database user passwords are stored as MD5 hashes, so the administrator cannot determine the actual password assigned to the user. If MD5 encryption is used f

The xml2 module provides XPath querying and XSLT functionality. F.46.1. Deprecation Notice From PostgreSQL 8.3 on, there is XML-related functionality based on the SQL/XML standard in the core server. That functionality covers XML syntax checking and XPath queries, which is what this module does, and more, but the API is not at all compatible. It is planned that this module will be removed in a future version of PostgreSQL in favor of the newer standard API, so you are encouraged to try convert

When you create complex database structures involving many tables with foreign key constraints, views, triggers, functions, etc. you implicitly create a net of dependencies between the objects. For instance, a table with a foreign key constraint depends on the table it references. To ensure the integrity of the entire database structure, PostgreSQL makes sure that you cannot drop objects that other objects still depend on. For example, attempting to drop the products table we considered in Sect