Search PostgreSQL documentation

tsm_system_time — the SYSTEM_TIME sampling method for TABLESAMPLEManual ChaptersPG18F.47. tsm_system_time — the SYSTEM_TIME sampling method for TABLESAMPLE The tsm_system_time module provides the table sampling method SYSTEM_TIME, which can be used in the TABLESAMPLE clause…tsm_system_time — the SYSTEM_TIME sampling method for TABLESAMPLE tsqueryManual ChaptersPG188.11.2. tsquery A tsquery value stores lexemes that are to be searched for, and can combine them using the Boolean operators & (AND), | (OR), and ! (NOT), as well as the phrase search operat…tsquery tsvectorManual ChaptersPG188.11.1. tsvector A tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word (see Chapter 12 for details). S…tsvector TuplesManual ChaptersPG18Tuples Each tuple begins with a 16-bit integer count of the number of fields in the tuple. (Presently, all tuples in a table will have the same count, but that might not always be true.) The…Tuples TutorialManual ChaptersPG18Part I. Tutorial Welcome to the PostgreSQL Tutorial. The tutorial is intended to give an introduction to PostgreSQL, relational database concepts, and the SQL language. We assume some genera…Tutorial Two-phase Commit Support for Logical DecodingManual ChaptersPG1847.10. Two-phase Commit Support for Logical Decoding With the basic output plugin callbacks (eg., begin_cb, change_cb, commit_cb and message_cb) two-phase commit commands like PREPARE TRANSA…Two-phase Commit Support for Logical Decoding Two-Phase TransactionsManual ChaptersPG1867.4. Two-Phase Transactions PostgreSQL supports a two-phase commit (2PC) protocol that allows multiple distributed systems to work together in a transactional manner. The commands are PREPA…Two-Phase Transactions TYPEManual ChaptersPG18TYPE TYPE — define a new data type Synopsis TYPE type_name IS ctypeTYPE Type CastsManual ChaptersPG184.2.9. Type Casts A type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent syntaxes for type casts: CAST ( expression AS type ) expression::type Th…Type Casts Type ConversionManual ChaptersPG18Chapter 10. Type Conversion SQL statements can, intentionally or not, require the mixing of different data types in the same expression. PostgreSQL has extensive facilities for evaluating mi…Type Conversion Type MappingManual ChaptersPG1834.4.4. Type Mapping When ECPG applications exchange values between the PostgreSQL server and the C application, such as when retrieving query results from the server or executing SQL statem…Type Mapping Type SafetyManual ChaptersPG188.7.3. Type Safety Each enumerated data type is separate and cannot be compared with other enumerated types. See this example: CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic…Type Safety Typed TablesManual ChaptersPG18Typed Tables Typed tables implement a subset of the SQL standard. According to the standard, a typed table has columns corresponding to the underlying composite type as well as one other col…Typed Tables TypedefsManual ChaptersPG1834.4.4.3.3. Typedefs Use the typedef keyword to map new types to already existing types. EXEC SQL BEGIN DECLARE SECTION; typedef char mychartype[40]; typedef long serial_t; EXEC SQL END DECL…Typedefs udt_privilegesManual ChaptersPG1835.58. udt_privileges The view udt_privileges identifies USAGE privileges granted on user-defined types to a currently enabled role or by a currently enabled role. There is one row for each …udt_privileges unaccent — a text search dictionary which removes diacriticsManual ChaptersPG18F.48. unaccent — a text search dictionary which removes diacritics unaccent is a text search dictionary that removes accents (diacritic signs) from lexemes. It's a filtering dictionary, whic…unaccent — a text search dictionary which removes diacritics unicode-modeManual ChaptersPG18F.26.3.8.11. unicode-mode Whether to convert textual data from database internal encoding to UTF-8 and back. If your database already is UTF-8, no conversion will be done, but the message wi…unicode-mode UNION ClauseManual ChaptersPG18UNION Clause The UNION clause has this general form: select_statement UNION [ ALL | DISTINCT ] select_statement select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KE…UNION Clause UNION, CASE, and Related ConstructsManual ChaptersPG1810.5. UNION, CASE, and Related Constructs SQL UNION constructs must match up possibly dissimilar types to become a single result set. The resolution algorithm is applied separately to each o…UNION, CASE, and Related Constructs Unique ConstraintsManual ChaptersPG185.5.3. Unique Constraints Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. The syntax is: CREATE TABLE product…Unique Constraints Unique IndexesManual ChaptersPG1811.6. Unique Indexes Indexes can also be used to enforce uniqueness of a column's value, or the uniqueness of the combined values of more than one column. CREATE UNIQUE INDEX name ON table (…Unique Indexes UNLISTENManual ChaptersPG18UNLISTEN UNLISTEN — stop listening for a notification Synopsis UNLISTEN { channel | * }UNLISTEN Unsupported FeaturesManual ChaptersPG18D.2. Unsupported Features The following features defined in SQL:2023 are not implemented in this release of PostgreSQL. In a few cases, equivalent functionality is available. Identifier Core…Unsupported Features Updatability OptionsManual ChaptersPG18F.38.1.7. Updatability Options By default all foreign tables using postgres_fdw are assumed to be updatable. This may be overridden using the following option: updatable (boolean) This optio…Updatability Options Updatable ViewsManual ChaptersPG18Updatable Views Simple views are automatically updatable: the system will allow INSERT, UPDATE, DELETE, and MERGE statements to be used on the view in the same way as on a regular table. A v…Updatable Views UPDATEManual ChaptersPG18UPDATE UPDATE — update rows of a table Synopsis [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( c…UPDATE UPDATE TransformationsManual ChaptersPG1829.4.3. UPDATE Transformations Whenever an UPDATE is processed, the row filter expression is evaluated for both the old and new row (i.e. using the data before and after the update). If both…UPDATE Transformations UPDATE/DELETE WHERE CURRENT OFManual ChaptersPG1841.7.3.3. UPDATE/DELETE WHERE CURRENT OF UPDATE table SET ... WHERE CURRENT OF cursor; DELETE FROM table WHERE CURRENT OF cursor; When a cursor is positioned on a table row, that row can be …UPDATE/DELETE WHERE CURRENT OF UpdatesManual ChaptersPG182.8. Updates You can update existing rows using the UPDATE command. Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as foll…Updates Updating a ViewManual ChaptersPG1839.2.4. Updating a View What happens if a view is named as the target relation for an INSERT, UPDATE, DELETE, or MERGE? Doing the substitutions described above would give a query tree in whi…Updating a View Updating DataManual ChaptersPG186.2. Updating Data The modification of data that is already in the database is referred to as updating. You can update individual rows, all the rows in a table, or a subset of all rows. Each…Updating Data Updating Planner StatisticsManual ChaptersPG1824.1.3. Updating Planner Statistics The PostgreSQL query planner relies on statistical information about the contents of tables in order to generate good plans for queries. These statistics …Updating Planner Statistics Updating the Visibility MapManual ChaptersPG1824.1.4. Updating the Visibility Map Vacuum maintains a visibility map for each table to keep track of which pages contain only tuples that are known to be visible to all active transactions …Updating the Visibility Map UpgradeManual ChaptersPG1829.13. Upgrade Migration of logical replication clusters is possible only when all the members of the old logical replication clusters are version 17.0 or later.Upgrade Upgrading a PostgreSQL ClusterManual ChaptersPG1818.6. Upgrading a PostgreSQL Cluster This section discusses how to upgrade your database data from one PostgreSQL release to a newer one. Current PostgreSQL version numbers consist of a majo…Upgrading a PostgreSQL Cluster Upgrading Data via pg_dumpallManual ChaptersPG1818.6.1. Upgrading Data via pg_dumpall One upgrade method is to dump data from one major version of PostgreSQL and restore it in another — to do this, you must use a logical backup tool like …Upgrading Data via pg_dumpall Upgrading Data via pg_upgradeManual ChaptersPG1818.6.2. Upgrading Data via pg_upgrade The pg_upgrade module allows an installation to be migrated in-place from one major PostgreSQL version to another. Upgrades can be performed in minutes,…Upgrading Data via pg_upgrade Upgrading Data via ReplicationManual ChaptersPG1818.6.3. Upgrading Data via Replication It is also possible to use logical replication methods to create a standby server with the updated version of PostgreSQL. This is possible because logi…Upgrading Data via Replication Upgrading Logical Replication ClustersManual ChaptersPG1829.13.3. Upgrading Logical Replication Clusters While upgrading a subscriber, write operations can be performed in the publisher. These changes will be replicated to the subscriber once the …Upgrading Logical Replication Clusters UsageManual ChaptersPG18UsageUsage