Search PostgreSQL documentation

Implementation FeaturesManual ChaptersPG1833.2. Implementation Features The large object implementation breaks large objects up into “chunks” and stores the chunks in rows in the database. A B-tree index guarantees fast searches for…Implementation Features Implicit Rollback after ExceptionsManual ChaptersPG1841.13.2.1. Implicit Rollback after Exceptions In PL/pgSQL, when an exception is caught by an EXCEPTION clause, all database changes since the block's BEGIN are automatically rolled back. Tha…Implicit Rollback after Exceptions IMPORT FOREIGN SCHEMAManual ChaptersPG18IMPORT FOREIGN SCHEMA IMPORT FOREIGN SCHEMA — import table definitions from a foreign server Synopsis IMPORT FOREIGN SCHEMA remote_schema [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] FRO…IMPORT FOREIGN SCHEMA Importing a Large ObjectManual ChaptersPG1833.3.2. Importing a Large Object To import an operating system file as a large object, call Oid lo_import(PGconn *conn, const char *filename); filename specifies the operating system name of…Importing a Large Object Importing OptionsManual ChaptersPG18F.38.1.9. Importing Options postgres_fdw is able to import foreign table definitions using IMPORT FOREIGN SCHEMA. This command creates foreign table definitions on the local server that matc…Importing Options INManual ChaptersPG189.25.1. IN expression IN (value [, ...]) The right-hand side is a parenthesized list of expressions. The result is “true” if the left-hand expression's result is equal to any of the right-ha…IN INManual ChaptersPG189.24.2. IN expression IN (subquery) The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of t…IN Incidental Limits of the ImplementationManual ChaptersPG18D.3.2. Incidental Limits of the Implementation This section concerns limits that are not inherent in the libxml2 library, but apply to the current implementation in PostgreSQL.Incidental Limits of the Implementation Including FilesManual ChaptersPG1834.9.1. Including Files To include an external file into your embedded SQL program, use: EXEC SQL INCLUDE filename; EXEC SQL INCLUDE <filename>; EXEC SQL INCLUDE "filename"; The embedded SQL…Including Files Inclusive and Exclusive BoundsManual ChaptersPG188.17.3. Inclusive and Exclusive Bounds Every non-empty range has two bounds, the lower bound and the upper bound. All points between these values are included in the range. An inclusive boun…Inclusive and Exclusive Bounds Increase maintenance_work_memManual ChaptersPG1814.4.5. Increase maintenance_work_mem Temporarily increasing the maintenance_work_mem configuration variable when loading large amounts of data can lead to improved performance. This will he…Increase maintenance_work_mem Increase max_wal_sizeManual ChaptersPG1814.4.6. Increase max_wal_size Temporarily increasing the max_wal_size configuration variable can also make large data loads faster. This is because loading a large amount of data into Postgr…Increase max_wal_size Index Access Method FunctionsManual ChaptersPG1863.2. Index Access Method Functions The index construction and maintenance functions that an index access method must provide in IndexAmRoutine are: IndexBuildResult * ambuild (Relation heap…Index Access Method Functions Index Access Method Interface DefinitionManual ChaptersPG18Chapter 63. Index Access Method Interface Definition This chapter defines the interface between the core PostgreSQL system and index access methods, which manage individual index types. The …Index Access Method Interface Definition Index Cost Estimation FunctionsManual ChaptersPG1863.6. Index Cost Estimation Functions The amcostestimate function is given information describing a possible index scan, including lists of WHERE and ORDER BY clauses that have been determin…Index Cost Estimation Functions Index Locking ConsiderationsManual ChaptersPG1863.4. Index Locking Considerations Index access methods must handle concurrent updates of the index by multiple processes. The core PostgreSQL system obtains AccessShareLock on the index dur…Index Locking Considerations Index MaintenanceManual ChaptersPG1865.5.1.1. Index Maintenance At the time of creation, all existing heap pages are scanned and a summary index tuple is created for each range, including the possibly-incomplete range at the e…Index Maintenance Index Maintenance FunctionsManual ChaptersPG189.28.8. Index Maintenance Functions Table 9.107 shows the functions available for index maintenance tasks. (Note that these maintenance tasks are normally done automatically by autovacuum; u…Index Maintenance Functions Index Method StrategiesManual ChaptersPG1836.16.2. Index Method Strategies The operators associated with an operator class are identified by “strategy numbers”, which serve to identify the semantics of each operator within the conte…Index Method Strategies Index Method Support RoutinesManual ChaptersPG1836.16.3. Index Method Support Routines Strategies aren't usually enough information for the system to figure out how to use an index. In practice, the index methods require additional suppor…Index Method Support Routines Index Methods and Operator ClassesManual ChaptersPG1836.16.1. Index Methods and Operator Classes Operator classes are associated with an index access method, such as B-Tree or GIN. Custom index access method may be defined with CREATE ACCESS M…Index Methods and Operator Classes Index ScanningManual ChaptersPG1863.3. Index Scanning In an index scan, the index access method is responsible for regurgitating the TIDs of all the tuples it has been told about that match the scan keys. The access method …Index Scanning Index Storage ParametersManual ChaptersPG18Index Storage Parameters The optional WITH clause specifies storage parameters for the index. Each index method has its own set of allowed storage parameters. The B-tree, hash, GiST and SP-G…Index Storage Parameters Index SupportManual ChaptersPG18F.19.2. Index Support intarray provides index support for the &&, @>, and @@ operators, as well as regular array equality. Two parameterized GiST index operator classes are provided: gist__i…Index Support Index SupportManual ChaptersPG18F.35.4. Index Support The pg_trgm module provides GiST and GIN index operator classes that allow you to create an index over a text column for the purpose of very fast similarity searches. T…Index Support Index TypesManual ChaptersPG1811.2. Index Types PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension bloom. Each index type uses a different algorithm that is best suited to …Index Types Index Uniqueness ChecksManual ChaptersPG1863.5. Index Uniqueness Checks PostgreSQL enforces SQL uniqueness constraints using unique indexes, which are indexes that disallow multiple entries with identical keys. An access method that…Index Uniqueness Checks Index-Only Scans and Covering IndexesManual ChaptersPG1811.9. Index-Only Scans and Covering Indexes All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area (which is called the…Index-Only Scans and Covering Indexes IndexesManual ChaptersPG18F.17.3. Indexes hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators. For example: CREATE INDEX hidx ON testhstore USING GIST (h); CREATE INDEX hidx ON testhstore USING G…Indexes IndexesManual ChaptersPG18Chapter 11. Indexes Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an i…Indexes IndexesManual ChaptersPG18F.22.3. Indexes ltree supports several types of indexes that can speed up the indicated operators: B-tree index over ltree: <, <=, =, >=, > Hash index over ltree: = GiST index over ltree (gi…Indexes IndexesManual ChaptersPG18E.6.3.1.2. Indexes Allow skip scans of btree indexes (Peter Geoghegan) § § This allows multi-column btree indexes to be used in more cases such as when there are no restrictions on the first…Indexes Indexes and CollationsManual ChaptersPG1811.11. Indexes and Collations An index can support only one collation per index column. If multiple collations are of interest, multiple indexes may be needed. Consider these statements: CRE…Indexes and Collations Indexes and ORDER BYManual ChaptersPG1811.4. Indexes and ORDER BY In addition to simply finding the rows to be returned by a query, an index may be able to deliver them in a specific sorted order. This allows a query's ORDER BY s…Indexes and ORDER BY Indexes on ExpressionsManual ChaptersPG1811.7. Indexes on Expressions An index column need not be just a column of the underlying table, but can be a function or scalar expression computed from one or more columns of the table. Thi…Indexes on Expressions IndexingManual ChaptersPG188.17.9. Indexing GiST and SP-GiST indexes can be created for table columns of range types. GiST indexes can be also created for table columns of multirange types. For instance, to create a G…Indexing IndicatorsManual ChaptersPG1834.4.6. Indicators The examples above do not handle null values. In fact, the retrieval examples will raise an error if they fetch a null value from the database. To be able to pass null val…Indicators inetManual ChaptersPG188.9.1. inet The inet type holds an IPv4 or IPv6 host address, and optionally its subnet, all in one field. The subnet is represented by the number of network address bits present in the host…inet inet vs. cidrManual ChaptersPG188.9.3. inet vs. cidr The essential difference between inet and cidr data types is that inet accepts values with nonzero bits to the right of the netmask, whereas cidr does not. For example, …inet vs. cidr Infinite (Unbounded) RangesManual ChaptersPG188.17.4. Infinite (Unbounded) Ranges The lower bound of a range can be omitted, meaning that all values less than the upper bound are included in the range, e.g., (,3]. Likewise, if the upper…Infinite (Unbounded) Ranges