Search PostgreSQL documentation

Migration to Version 18Manual ChaptersPG18E.6.2. Migration to Version 18 A dump/restore using pg_dumpall or use of pg_upgrade or logical replication is required for those wishing to migrate data from any previous release. See Sectio…Migration to Version 18 Migration to Version 18.1Manual ChaptersPG18E.5.1. Migration to Version 18.1 A dump/restore is not required for those running 18.X.Migration to Version 18.1 Migration to Version 18.2Manual ChaptersPG18E.4.1. Migration to Version 18.2 A dump/restore is not required for those running 18.X. However, if you have any indexes on ltree columns, it may be necessary to reindex them after updating.…Migration to Version 18.2 Migration to Version 18.3Manual ChaptersPG18E.3.1. Migration to Version 18.3 A dump/restore is not required for those running 18.X. However, if you are upgrading from a version earlier than 18.2, see Section E.4.Migration to Version 18.3 Migration to Version 18.4Manual ChaptersPG18E.2.1. Migration to Version 18.4 A dump/restore is not required for those running 18.X. However, if you are upgrading from a version earlier than 18.2, see Section E.4.Migration to Version 18.4 Migration to Version 18.6Manual ChaptersPG18E.1.1. Migration to Version 18.6 A dump/restore is not required for those running 18.X. However, the first three security entries below describe configuration adjustments and data cleanups t…Migration to Version 18.6 MinGWManual ChaptersPG1817.7.3. MinGW PostgreSQL for Windows can be built using MinGW, a Unix-like build environment for Windows. It is recommended to use the MSYS2 environment for this and also to install any prer…MinGW Minor VersionsManual ChaptersPG1836.10.6.2.2. Minor Versions PostgreSQL makes an effort to avoid server API breaks in minor releases. In general, extension code that compiles and works with a minor release should also compi…Minor Versions Minor VersionsManual ChaptersPG1836.10.6.3.2. Minor Versions PostgreSQL makes an effort to avoid server ABI breaks in minor releases. In general, an extension compiled against any minor release should work with any other mi…Minor Versions MiscellaneousManual ChaptersPG1817.3.3.5. Miscellaneous It's fairly common, particularly for test builds, to adjust the default port number with --with-pgport. The other options in this section are recommended only for adv…Miscellaneous MiscellaneousManual ChaptersPG1817.4.3.6. Miscellaneous -Dpgport=NUMBER # Set NUMBER as the default port number for server and clients. The default is 5432. The port can always be changed later on, but if you specify it he…Miscellaneous MiscellaneousManual ChaptersPG18F.40.5.6. Miscellaneous We reject the LOAD command across the board, because any module loaded could easily circumvent security policy enforcement.Miscellaneous Miscellaneous Coding ConventionsManual ChaptersPG1855.4. Miscellaneous Coding Conventions C Standard # Code in PostgreSQL should only rely on language features available in the C99 standard. That means a conforming C99 compiler has to be abl…Miscellaneous Coding Conventions Miscellaneous FunctionsManual ChaptersPG1832.12. Miscellaneous Functions As always, there are some functions that just don't fit anywhere. PQfreemem Frees memory allocated by libpq. void PQfreemem(void *ptr); Frees memory allocated …Miscellaneous Functions moddatetime — Functions for Tracking Last Modification TimeManual ChaptersPG18F.41.4. moddatetime — Functions for Tracking Last Modification Time moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last m…moddatetime — Functions for Tracking Last Modification Time Modifying ArraysManual ChaptersPG188.15.4. Modifying Arrays An array value can be replaced completely: UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol'; or using the ARRAY expression syntax…Modifying Arrays Modifying Composite TypesManual ChaptersPG188.16.4. Modifying Composite Types Here are some examples of the proper syntax for inserting and updating composite columns. First, inserting or updating a whole column: INSERT INTO mytab (co…Modifying Composite Types Modifying TablesManual ChaptersPG185.7. Modifying Tables When you create a table and you realize that you made a mistake, or the requirements of the application change, you can drop the table and create it again. But this is …Modifying Tables Monetary TypesManual ChaptersPG188.2. Monetary Types The money type stores a currency amount with a fixed fractional precision; see Table 8.3. The fractional precision is determined by the database's lc_monetary setting. Th…Monetary Types MonitoringManual ChaptersPG1829.10. Monitoring Because logical replication is based on a similar architecture as physical streaming replication, the monitoring on a publication node is similar to monitoring of a physica…Monitoring MonitoringManual ChaptersPG18E.6.3.1.4. Monitoring Increase the logging granularity of server variable log_connections (Melanie Plageman) § This server variable was previously only boolean, which is still supported. Add…Monitoring MonitoringManual ChaptersPG1826.2.5.2. Monitoring An important health indicator of streaming replication is the amount of WAL records generated in the primary, but not yet applied in the standby. You can calculate this …Monitoring Monitoring Database ActivityManual ChaptersPG18Chapter 27. Monitoring Database Activity A database administrator frequently wonders, “What is the system doing right now?” This chapter discusses how to find that out. Several tools are ava…Monitoring Database Activity Monitoring Disk UsageManual ChaptersPG1827.6. Monitoring Disk Usage This section discusses how to monitor the disk usage of a PostgreSQL database system.Monitoring Disk Usage MOVEManual ChaptersPG1841.7.3.2. MOVE MOVE [ direction { FROM | IN } ] cursor; MOVE repositions a cursor without retrieving any data. MOVE works like the FETCH command, except it only repositions the cursor and do…MOVE MOVEManual ChaptersPG18MOVE MOVE — position a cursor Synopsis MOVE [ direction ] [ FROM | IN ] cursor_name where direction can be one of: NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count count ALL FORWARD FORWA…MOVE Moving-Aggregate ModeManual ChaptersPG1836.12.1. Moving-Aggregate Mode Aggregate functions can optionally support moving-aggregate mode, which allows substantially faster execution of aggregate functions within windows with moving…Moving-Aggregate Mode Multicolumn IndexesManual ChaptersPG1811.3. Multicolumn Indexes An index can be defined on more than one column of a table. For example, if you have a table of this form: CREATE TABLE test2 ( major int, minor int, name varchar )…Multicolumn Indexes Multiple Identity ColumnsManual ChaptersPG18Multiple Identity Columns PostgreSQL allows a table to have more than one identity column. The standard specifies that a table can have at most one identity column. This is relaxed mainly to…Multiple Identity Columns Multiple Statements in a Simple QueryManual ChaptersPG1854.2.2.1. Multiple Statements in a Simple Query When a simple Query message contains more than one SQL statement (separated by semicolons), those statements are executed as a single transact…Multiple Statements in a Simple Query Multiple Synchronous StandbysManual ChaptersPG1826.2.8.2. Multiple Synchronous Standbys Synchronous replication supports one or more synchronous standby servers; transactions will wait until all the standby servers which are considered as…Multiple Synchronous Standbys Multivalued ResultsManual ChaptersPG18F.50.3.1. Multivalued Results The xpath_table function assumes that the results of each XPath query might be multivalued, so the number of rows returned by the function may not be the same a…Multivalued Results Multivariate MCV ListsManual ChaptersPG1814.2.2.3. Multivariate MCV Lists Another type of statistic stored for each column are most-common value lists. This allows very accurate estimates for individual columns, but may result in s…Multivariate MCV Lists Multivariate N-Distinct CountsManual ChaptersPG1869.2.2. Multivariate N-Distinct Counts A similar problem occurs with estimation of the cardinality of sets of multiple columns, such as the number of groups that would be generated by a GROU…Multivariate N-Distinct Counts Multivariate N-Distinct CountsManual ChaptersPG1814.2.2.2. Multivariate N-Distinct Counts Single-column statistics store the number of distinct values in each column. Estimates of the number of distinct values when combining more than one …Multivariate N-Distinct Counts Multivariate Statistics ExamplesManual ChaptersPG1869.2. Multivariate Statistics ExamplesMultivariate Statistics Examples Multixacts and WraparoundManual ChaptersPG1824.1.5.1. Multixacts and Wraparound Multixact IDs are used to support row locking by multiple transactions. Since there is only limited space in a tuple header to store lock information, tha…Multixacts and Wraparound Named SQL Descriptor AreasManual ChaptersPG1834.7.1. Named SQL Descriptor Areas A named SQL descriptor area consists of a header, which contains information concerning the entire descriptor, and one or more item descriptor areas, which…Named SQL Descriptor Areas Namespace Available to GROUP BY and ORDER BYManual ChaptersPG18Namespace Available to GROUP BY and ORDER BY In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while a GROUP BY clause can only use expressions based on…Namespace Available to GROUP BY and ORDER BY Native Language SupportManual ChaptersPG18Chapter 56. Native Language SupportNative Language Support