Search PostgreSQL documentation

ParametersManual ChaptersPG18REFRESH MATERIALIZED VIEWCONCURRENTLY Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which affects a lot of rows will tend to use fewer r… ParametersManual ChaptersPG18REINDEXINDEX Recreate the specified index. This form of REINDEX cannot be executed inside a transaction block when used with a partitioned index. TABLE Recreate all indexes of the specified table. … ParametersManual ChaptersPG18RELEASE SAVEPOINTsavepoint_name The name of the savepoint to release. ParametersManual ChaptersPG18RESETconfiguration_parameter Name of a settable run-time parameter. Available parameters are documented in Chapter 19 and on the SET reference page. ALL Resets all settable run-time parameters to… ParametersManual ChaptersPG18ROLLBACK PREPAREDtransaction_id The transaction identifier of the transaction that is to be rolled back. ParametersManual ChaptersPG18ROLLBACK TO SAVEPOINTsavepoint_name The savepoint to roll back to. ParametersManual ChaptersPG18ROLLBACKWORKTRANSACTION # Optional key words. They have no effect. AND CHAIN # If AND CHAIN is specified, a new (not aborted) transaction is immediately started with the same transaction characteris… ParametersManual ChaptersPG18SAVEPOINTsavepoint_name The name to give to the new savepoint. If savepoints with the same name already exist, they will be inaccessible until newer identically-named savepoints are released. ParametersManual ChaptersPG18SECURITY LABELobject_nametable_name.column_nameaggregate_namefunction_nameprocedure_nameroutine_name The name of the object to be labeled. Names of objects that reside in schemas (tables, functions, etc.)… ParametersManual ChaptersPG18SELECT ParametersManual ChaptersPG18SELECT INTOTEMPORARY or TEMP If specified, the table is created as a temporary table. Refer to CREATE TABLE for details. UNLOGGED If specified, the table is created as an unlogged table. Refer to CREAT… ParametersManual ChaptersPG18SETSESSION Specifies that the command takes effect for the current session. (This is the default if neither SESSION nor LOCAL appears.) LOCAL Specifies that the command takes effect for only th… ParametersManual ChaptersPG18SHOWname The name of a run-time parameter. Available parameters are documented in Chapter 19 and on the SET reference page. In addition, there are a few parameters that can be shown but not set:… ParametersManual ChaptersPG18START TRANSACTIONRefer to SET TRANSACTION for information on the meaning of the parameters to this statement. ParametersManual ChaptersPG18TRUNCATEname The name (optionally schema-qualified) of a table to truncate. If ONLY is specified before the table name, only that table is truncated. If ONLY is not specified, the table and all its … ParametersManual ChaptersPG18UNLISTENchannel Name of a notification channel (any identifier). * All current listen registrations for this session are cleared. ParametersManual ChaptersPG18UPDATEwith_query The WITH clause allows you to specify one or more subqueries that can be referenced by name in the UPDATE query. See Section 7.8 and SELECT for details. table_name The name (optio… ParametersManual ChaptersPG18VACUUMFULL Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the… ParametersManual ChaptersPG18VALUESexpression A constant or expression to compute and insert at the indicated place in the resulting table (set of rows). In a VALUES list appearing at the top level of an INSERT, an expression… pargresExtensionsExtensionsAnalytics · ExtensionsPrototype parallel query execution module for shared-nothing PostgreSQL clusters executor parallel-query planner preload prototype pargres Analytics parodydbExtensionsExtensionsFull-text search · ExtensionsA toy full-text search extension for Postgres, written in Rust using pgrx parodydb Full-text search parquet_fdwExtensionsExtensionsForeign data wrappers · ExtensionsRead-only foreign data wrapper for Apache Parquet files parquet_fdw Foreign data wrappers parquet_ioExtensionsExtensionsReplication and streams · ExtensionsImport Parquet rows and export SQL results with Apache Arrow parquet arrow import-export linkmerce Replication and streams parquet_s3_fdwExtensionsExtensionsForeign data wrappers · ExtensionsForeign data wrapper for accessing Parquet files on local filesystems and Amazon S3 from PostgreSQL. fdw parquet pgspider s3 parquet_s3_fdw Foreign data wrappers parray_ginExtensionsExtensionsFunctions and aggregates · ExtensionsGIN index operator class and partial-match operators for text arrays array gin index parray_gin Functions and aggregates ParserManual ChaptersPG18The Parser StageThe parser has to check the query string (which arrives as plain text) for valid syntax. If the syntax is correct a parse tree is built up and handed back; otherwise an error is returned. Th… Parser TestingManual ChaptersPG18Testing and Debugging Text SearchThe following functions allow direct testing of a text search parser. ts_parse(parser_name text, document text, OUT tokid integer, OUT token text) returns setof record ts_parse(parser_oid oi… ParsersManual ChaptersPG18Text search parsers are responsible for splitting raw document text into tokens and identifying each token's type, where the set of possible types is defined by the parser itself. Note that … Parsing DocumentsManual ChaptersPG18Controlling Text SearchPostgreSQL provides the function to_tsvector for converting a document to the tsvector data type. to_tsvector([ config regconfig, ] document text) returns tsvector to_tsvector parses a textu… Parsing QueriesManual ChaptersPG18Controlling Text SearchPostgreSQL provides the functions to_tsquery, plainto_tsquery, phraseto_tsquery and websearch_to_tsquery for converting a query to the tsquery data type. to_tsquery offers access to more fea… Partial AggregationManual ChaptersPG18User-Defined AggregatesOptionally, an aggregate function can support partial aggregation. The idea of partial aggregation is to run the aggregate's state transition function over different subsets of the input dat… Partial IndexesManual ChaptersPG18A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for… Partial Match AlgorithmManual ChaptersPG18GIN IndexesGIN can support “partial match” queries, in which the query does not determine an exact match for one or more keys, but the possible matches fall within a reasonably narrow range of key valu… PARTITION BYSQL CommandsPG18CREATE TABLE › Compatibility › PARTITION BY ClauseClause The PARTITION BY clause is a PostgreSQL extension. PARTITION BY ClauseManual ChaptersPG18CREATE TABLEThe PARTITION BY clause is a PostgreSQL extension. Partition MaintenanceManual ChaptersPG18Table PartitioningNormally the set of partitions established when initially defining the table is not intended to remain static. It is common to want to remove partitions holding old data and periodically add… PARTITION OFSQL CommandsPG18CREATE TABLE › Compatibility › PARTITION OF ClauseClause The PARTITION OF clause is a PostgreSQL extension. PARTITION OF ClauseManual ChaptersPG18CREATE TABLEThe PARTITION OF clause is a PostgreSQL extension. Partition PruningManual ChaptersPG18Table Partitioningis a query optimization technique that improves performance for declaratively partitioned tables. As an example: SET enable_partition_pruning = on; -- the default SELECT count(*) FROM measur… Partitioned TablesManual ChaptersPG18Row FiltersIf the publication contains a partitioned table, the publication parameter publish_via_partition_root determines which row filter is used. If publish_via_partition_root is true, the root par…