PostgreSQL provides the standard SQL type boolean; see Table 8-19. The boolean type can have several states: "true", "false", and a third state, "unknown", which is represented by the SQL null value. Table 8-19. Boolean Data Type Name Storage Size Description boolean 1 byte state of true or false Valid literal values for the "true" state are: TRUE 't' 'true' 'y' 'yes' 'on' '1' For the "false" state, the following values can be used: FALSE 'f' 'false' 'n' 'no' 'off' '0' Leading or traili

For convenience there are also single letter command-line option switches available for some parameters. They are described in Table 19-2. Some of these options exist for historical reasons, and their presence as a single-letter option does not necessarily indicate an endorsement to use the option heavily. Table 19-2. Short Option Key Short Option Equivalent -B x shared_buffers = x -d x log_min_messages = DEBUGx -e datestyle = euro -fb, -fh, -fi, -fm, -fn, -fo, -fs, -ft enable_bitmapscan =

The tcn module provides a trigger function that notifies listeners of changes to any table on which it is attached. It must be used as an AFTER trigger FOR EACH ROW. Only one parameter may be supplied to the function in a CREATE TRIGGER statement, and that is optional. If supplied it will be used for the channel name for the notifications. If omitted tcn will be used for the channel name. The payload of the notifications consists of the table name, a letter to indicate which type of operation w

The catalog pg_largeobject holds the data making up "large objects". A large object is identified by an OID assigned when it is created. Each large object is broken into segments or "pages" small enough to be conveniently stored as rows in pg_largeobject. The amount of data per page is defined to be LOBLKSIZE (which is currently BLCKSZ/4, or typically 2 kB). Prior to PostgreSQL 9.0, there was no permission structure associated with large objects. As a result, pg_largeobject was publicly readabl

The PostgreSQL source distribution includes several examples of index operator classes for SP-GiST, as described in Table 62-1. Look into src/backend/access/spgist/ and src/backend/utils/adt/ to see the code. Prev Next Implementation Up GIN Indexes

NameDROP INDEX -- remove an index Synopsis DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] Description DROP INDEX drops an existing index from the database system. To execute this command you must be the owner of the index. Parameters CONCURRENTLY Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal DROP INDEX acquires exclusive lock on the table, blocking other accesses until the index d

The core PostgreSQL distribution includes the BRIN operator classes shown in Table 64-1. The minmax operator classes store the minimum and the maximum values appearing in the indexed column within the range. The inclusion operator classes store a value which includes the values in the indexed column within the range. Table 64-1. Built-in BRIN Operator Classes Name Indexed Data Type Indexable Operators abstime_minmax_ops abstime < <= = >= > int8_minmax_ops bigint < <= = &g

NameLOAD -- load a shared library file Synopsis LOAD 'filename' Description This command loads a shared library file into the PostgreSQL server's address space. If the file has been loaded already, the command does nothing. Shared library files that contain C functions are automatically loaded whenever one of their functions is called. Therefore, an explicit LOAD is usually only needed to load a library that modifies the server's behavior through "hooks" rather than providing a set of

Currently PostgreSQL provides one built in trigger function, suppress_redundant_updates_trigger, which will prevent any update that does not actually change the data in the row from taking place, in contrast to the normal behavior which always performs the update regardless of whether or not the data has changed. (This normal behavior makes updates run faster, since no checking is required, and is also useful in certain cases.) Ideally, you should normally avoid running updates that don't actua

NameDROP TRANSFORM -- remove a transform Synopsis DROP TRANSFORM [ IF EXISTS ] FOR type_name LANGUAGE lang_name [ CASCADE | RESTRICT ] Description DROP TRANSFORM removes a previously defined transform. To be able to drop a transform, you must own the type and the language. These are the same privileges that are required to create a transform. Parameters IF EXISTS Do not throw an error if the transform does not exist. A notice is issued in this case. type_name The name of the da