See Section 8.17 for an overview of range types. Table 9-49 shows the operators available for range types. Table 9-49. Range Operators Operator Description Example Result = equal int4range(1,5) = '[1,4]'::int4range t <> not equal numrange(1.1,2.2) <> numrange(1.1,2.3) t < less than int4range(1,10) < int4range(2,3) t > greater than int4range(1,10) > int4range(1,5) t <= less than or equal numrange(1.1,2.2) <= numrange(1.1,2.2) t >= greater than or equal numrange

The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms. There are also functions to produce certain special UUID constants. F.45.1. uuid-ossp Functions Table F-33 shows the functions available to generate UUIDs. The relevant standards ITU-T Rec. X.667, ISO/IEC 9834-8:2005, and RFC 4122 specify four algorithms for generating UUIDs, identified by the version numbers 1, 3, 4, and 5. (There is no version 2 algorithm.) Eac

The pageinspect module provides functions that allow you to inspect the contents of database pages at a low level, which is useful for debugging purposes. All of these functions may be used only by superusers. F.22.1. Functions get_raw_page(relname text, fork text, blkno int) returns bytea get_raw_page reads the specified block of the named relation and returns a copy as a bytea value. This allows a single time-consistent copy of the block to be obtained. fork should be 'main' for th

This section covers implementation details and other tricks that are useful for implementers of SP-GiST operator classes to know. 62.4.1. SP-GiST Limits Individual leaf tuples and inner tuples must fit on a single index page (8kB by default). Therefore, when indexing values of variable-length data types, long values can only be supported by methods such as radix trees, in which each level of the tree includes a prefix that is short enough to fit on a page, and the final leaf level includes a s

The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings. F.31.1. Trigram (or Trigraph) Concepts A trigram is a group of three consecutive characters taken from a string. We can measure the similarity of two strings by counting the number of trigrams they share. This simple idea turns out to be very effective for measuring the similarity of

Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database). To create a role use the CREATE ROLE SQL command: CREATE ROLE name; name follows the rules for SQL identifiers: either unadorned without special characters, or double-quoted. (In practice, you will usually want to add additio

Before you can do anything, you must initialize a database storage area on disk. We call this a database cluster. (The SQL standard uses the term catalog cluster.) A database cluster is a collection of databases that is managed by a single instance of a running database server. After initialization, a database cluster will contain a database named postgres, which is meant as a default database for use by utilities, users and third party applications. The database server itself does not require

Namepg_resetxlog -- reset the write-ahead log and other control information of a PostgreSQL database cluster Synopsis pg_resetxlog [-f] [-n] [option...] {[-D] datadir} Description pg_resetxlog clears the write-ahead log (WAL) and optionally resets some other control information stored in the pg_control file. This function is sometimes needed if these files have become corrupted. It should be used only as a last resort, when the server will not start due to such corruption. After running

This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are simply text strings. F.16.1. hstore External Representation The text representation of an hstore, used for input and output, includes zero or more key => value pairs separated by commas. Some examples: k => v foo => bar, baz =&g

NameCREATE OPERATOR CLASS -- define a new operator class Synopsis CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type USING index_method [ FAMILY family_name ] AS { OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ FOR SEARCH | FOR ORDER BY sort_family_name ] | FUNCTION support_number [ ( op_type [ , op_type ] ) ] function_name ( argument_type [, ...] ) | STORAGE storage_type } [, ... ] Description CREATE OPERATOR CLASS creates a new operator class.