Hot Standby is the term used to describe the ability to connect to the server and run read-only queries while the server is in archive recovery or standby mode. This is useful both for replication purposes and for restoring a backup to a desired state with great precision. The term Hot Standby also refers to the ability of the server to move from recovery through to normal operation while users continue running queries and/or keep their connections open. Running queries in hot standby mode is s

The SQL standard states that "Within the definition of a 'datetime literal', the 'datetime values' are constrained by the natural rules for dates and times according to the Gregorian calendar". PostgreSQL follows the SQL standard's lead by counting dates exclusively in the Gregorian calendar, even for years before that calendar was in use. This rule is known as the proleptic Gregorian calendar. The Julian calendar was introduced by Julius Caesar in 45 BC. It was in common use in the Western wor

NameGRANT -- define access privileges Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } ON [ TABLE ] table_name [, ...] TO role_s

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

The geometric types point, box, lseg, line, path, polygon, and circle have a large set of native support functions and operators, shown in Table 9-33, Table 9-34, and Table 9-35. Caution: Note that the "same as" operator, ~=, represents the usual notion of equality for the point, box, polygon, and circle types. Some of these types also have an = operator, but = compares for equal areas only. The other scalar comparison operators (<= and so on) likewise compare areas for these types. Tabl

The fuzzystrmatch module provides several functions to determine similarities and distance between strings. Caution: At present, the soundex, metaphone, dmetaphone, and dmetaphone_alt functions do not work well with multibyte encodings (such as UTF-8). F.15.1. Soundex The Soundex system is a method of matching similar-sounding names by converting them to the same code. It was initially used by the United States Census in 1880, 1900, and 1910. Note that Soundex is not very useful for non-Engl

Functions and triggers allow users to insert code into the backend server that other users might execute unintentionally. Hence, both mechanisms permit users to "Trojan horse" others with relative ease. The only real protection is tight control over who can define functions. Functions run inside the backend server process with the operating system permissions of the database server daemon. If the programming language used for the function allows unchecked memory accesses, it is possible to chan

The behavior of a custom text search configuration can easily become confusing. The functions described in this section are useful for testing text search objects. You can test a complete configuration, or test parsers and dictionaries separately. 12.8.1. Configuration Testing The function ts_debug allows easy testing of a text search configuration. ts_debug([ config regconfig, ] document text, OUT alias text, OUT description text, OUT token text, OUT dicti

The examples in the previous section illustrated full text matching using simple constant strings. This section shows how to search table data, optionally using indexes. 12.2.1. Searching a Table It is possible to do a full text search without an index. A simple query to print the title of each row that contains the word friend in its body field is: SELECT title FROM pgweb WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend'); This will also find related words such as friends

Information about text search configuration objects can be obtained in psql using a set of commands: \dF{d,p,t}[+] [PATTERN] An optional + produces more details. The optional parameter PATTERN can be the name of a text search object, optionally schema-qualified. If PATTERN is omitted then information about all visible objects will be displayed. PATTERN can be a regular expression and can provide separate patterns for the schema and object names. The following examples illustrate this: =>