Search PostgreSQL documentation

GIN FunctionsManual ChaptersPG18F.23.5. GIN Functions gin_metapage_info(page bytea) returns record gin_metapage_info returns information about a GIN index metapage. For example: test=# SELECT * FROM gin_metapage_info(get_r…GIN Functions GIN IndexesManual ChaptersPG1865.4. GIN IndexesGIN Indexes GIN Tips and TricksManual ChaptersPG1865.4.5. GIN Tips and Tricks Create vs. insert Insertion into a GIN index can be slow due to the likelihood of many keys being inserted for each item. So, for bulk insertions into a table it …GIN Tips and Tricks GiSTManual ChaptersPG1811.2.3. GiST GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operato…GiST GiST FunctionsManual ChaptersPG18F.23.6. GiST Functions gist_page_opaque_info(page bytea) returns record gist_page_opaque_info returns information from a GiST index page's opaque area, such as the NSN, rightlink and page ty…GiST Functions GiST Index Build MethodsManual ChaptersPG1865.2.4.1. GiST Index Build Methods The simplest way to build a GiST index is just to insert all the entries, one by one. This tends to be slow for large indexes, because if the index tuples …GiST Index Build Methods GiST IndexesManual ChaptersPG1865.2. GiST IndexesGiST Indexes Global Data in PL/TclManual ChaptersPG1842.4. Global Data in PL/Tcl Sometimes it is useful to have some global data that is held between two calls to a function or is shared between different functions. This is easily done in PL/T…Global Data in PL/Tcl Global Values in PL/PerlManual ChaptersPG1843.4. Global Values in PL/Perl You can use the global hash %_SHARED to store data, including code references, between function calls for the lifetime of the current session. Here is a simple…Global Values in PL/Perl GlossaryManual ChaptersPG18Appendix M. Glossary This is a list of terms and their meaning in the context of PostgreSQL and relational database systems in general. ACID Atomicity, Consistency, Isolation, and Durability…Glossary Good PracticesManual ChaptersPG18Good Practices It is very easy to use pgbench to produce completely meaningless numbers. Here are some guidelines to help you get useful results. In the first place, never believe any test t…Good Practices GRANTManual ChaptersPG18GRANT GRANT — define access privileges Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table…GRANT GRANT on Database ObjectsManual ChaptersPG18GRANT on Database Objects This variant of the GRANT command gives specific privileges on a database object to one or more roles. These privileges are added to those already granted, if any. …GRANT on Database Objects GRANT on RolesManual ChaptersPG18GRANT on Roles This variant of the GRANT command grants membership in a role to one or more other roles, and the modification of membership options SET, INHERIT, and ADMIN; see Section 21.3 …GRANT on Roles GREATEST and LEASTManual ChaptersPG189.18.4. GREATEST and LEAST GREATEST(value [, ...]) LEAST(value [, ...]) The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The ex…GREATEST and LEAST GROUP BY ClauseManual ChaptersPG18GROUP BY Clause The optional GROUP BY clause has the general form GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] GROUP BY will condense into a single row all selected rows that share t…GROUP BY Clause GROUPING SETS, CUBE, and ROLLUPManual ChaptersPG187.2.4. GROUPING SETS, CUBE, and ROLLUP More complex grouping operations than those described above are possible using the concept of grouping sets. The data selected by the FROM and WHERE cl…GROUPING SETS, CUBE, and ROLLUP GSSAPI AuthenticationManual ChaptersPG1820.6. GSSAPI Authentication GSSAPI is an industry-standard protocol for secure authentication defined in RFC 2743. PostgreSQL supports GSSAPI for authentication, communications encryption, o…GSSAPI Authentication GSSAPI Session EncryptionManual ChaptersPG1854.2.11. GSSAPI Session Encryption If PostgreSQL was built with GSSAPI support, frontend/backend communications can be encrypted using GSSAPI. This provides communication security in environ…GSSAPI Session Encryption GUC ParametersManual ChaptersPG18F.35.3. GUC Parameters pg_trgm.similarity_threshold (real) # Sets the current similarity threshold that is used by the % operator. The threshold must be between 0 and 1 (default is 0.3). pg_…GUC Parameters GUC ParametersManual ChaptersPG18F.40.4. GUC Parameters sepgsql.permissive (boolean) # This parameter enables sepgsql to function in permissive mode, regardless of the system setting. The default is off. This parameter can …GUC Parameters Handling a Table Rewrite EventManual ChaptersPG189.30.3. Handling a Table Rewrite Event The functions shown in Table 9.111 provide information about a table for which a table_rewrite event has just been called. If called in any other conte…Handling a Table Rewrite Event Handling Character StringsManual ChaptersPG1834.4.4.1. Handling Character Strings To handle SQL character string data types, such as varchar and text, there are two possible ways to declare the host variables. One way is using char[], …Handling Character Strings Handling Large DatabasesManual ChaptersPG1825.1.3. Handling Large Databases Some operating systems have maximum file size limits that cause problems when creating large pg_dump output files. Fortunately, pg_dump can write to the stan…Handling Large Databases Handling Nonprimitive SQL Data TypesManual ChaptersPG1834.4.5. Handling Nonprimitive SQL Data Types This section contains information on how to handle nonscalar and user-defined SQL-level data types in ECPG applications. Note that this is distin…Handling Nonprimitive SQL Data Types Handling of Invalid or Ambiguous TimestampsManual ChaptersPG18B.2. Handling of Invalid or Ambiguous Timestamps Ordinarily, if a date/time string is syntactically valid but contains out-of-range field values, an error will be thrown. For example, input …Handling of Invalid or Ambiguous Timestamps Handling of Quotation MarksManual ChaptersPG1841.12.1. Handling of Quotation Marks The code of a PL/pgSQL function is specified in CREATE FUNCTION as a string literal. If you write the string literal in the ordinary way with surrounding…Handling of Quotation Marks Handling Query ConflictsManual ChaptersPG1826.4.2. Handling Query Conflicts The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potentia…Handling Query Conflicts HashManual ChaptersPG1811.2.2. Hash Hash indexes store a 32-bit hash code derived from the value of the indexed column. Hence, such indexes can only handle simple equality comparisons. The query planner will consi…Hash Hash FunctionsManual ChaptersPG18F.23.7. Hash Functions hash_page_type(page bytea) returns text hash_page_type returns page type of the given HASH index page. For example: test=# SELECT hash_page_type(get_raw_page('con_hash…Hash Functions Hash IndexesManual ChaptersPG1865.6. Hash IndexesHash Indexes HASHESManual ChaptersPG1836.15.5. HASHES The HASHES clause, if present, tells the system that it is permissible to use the hash join method for a join based on this operator. HASHES only makes sense for a binary ope…HASHES HAVING ClauseManual ChaptersPG18HAVING Clause The optional HAVING clause has the general form HAVING condition where condition is the same as specified for the WHERE clause. HAVING eliminates group rows that do not satisfy…HAVING Clause Heap FunctionsManual ChaptersPG18F.23.2. Heap Functions heap_page_items(page bytea) returns setof record heap_page_items shows all line pointers on a heap page. For those line pointers that are in use, tuple headers as well…Heap Functions Heap-Only Tuples (HOT)Manual ChaptersPG1866.7. Heap-Only Tuples (HOT) To allow for high concurrency, PostgreSQL uses multiversion concurrency control (MVCC) to store rows. However, MVCC has some downsides for update queries. Specif…Heap-Only Tuples (HOT) High Availability, Load Balancing, and ReplicationManual ChaptersPG18Chapter 26. High Availability, Load Balancing, and Replication Database servers can work together to allow a second server to take over quickly if the primary server fails (high availability…High Availability, Load Balancing, and Replication Highlighting ResultsManual ChaptersPG1812.3.4. Highlighting Results To present search results it is ideal to show a part of each document and how it is related to the query. Usually, search engines show fragments of the document …Highlighting Results History of UnitsManual ChaptersPG18B.6. History of Units 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…History of Units hmac()Manual ChaptersPG18F.26.1.2. hmac() hmac(data text, key text, type text) returns bytea hmac(data bytea, key bytea, type text) returns bytea Calculates hashed MAC for data with key key. type is the same as in d…hmac() Hook TypesManual ChaptersPG1832.20.1.1. Hook Types The following PGauthData types and their corresponding data structures are defined: PQAUTHDATA_PROMPT_OAUTH_DEVICE # Replaces the default user prompt during the builtin…Hook Types