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

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

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

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 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

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

The current limitations of PostgreSQL's text search features are: The length of each lexeme must be less than 2K bytes The length of a tsvector (lexemes + positions) must be less than 1 megabyte The number of lexemes must be less than 264 Position values in tsvector must be greater than 0 and no more than 16,383 The match distance in a <N> (FOLLOWED BY) tsquery operator cannot be more than 16,384 No more than 256 positions per lexeme The number of nodes (lexemes + operators)