Search PostgreSQL documentation

MetaphoneManual ChaptersPG18fuzzystrmatch — determine string similarities and distance, like Soundex, is based on the idea of constructing a representative code for an input string. Two strings are then deemed similar if they have the same codes. This function calculates the … micromanageExtensionsExtensionsFeatures · Extensionsexample extension to inject plans planner protobuf query-plan pg_micromanage Features migrationExtensionsExtensionsReplication and streams · ExtensionsDatabase Migrations: Configure and Run migration schema versioning postgresql_migrations Replication and streams Migration to Version 18Manual ChaptersPG18Release 18A 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 Section 18.6 for general information … Migration to Version 18.1Manual ChaptersPG18Release 18.1A dump/restore is not required for those running 18.X. Migration to Version 18.2Manual ChaptersPG18Release 18.2A 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. See the sixth changelog entry be… Migration to Version 18.3Manual ChaptersPG18Release 18.3A 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.4Manual ChaptersPG18Release 18.4A 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.6Manual ChaptersPG18Release 18.6A dump/restore is not required for those running 18.X. However, the first three security entries below describe configuration adjustments and data cleanups that you may need to make after up… mimeoExtensionsExtensionsReplication and streams · ExtensionsExtension for specialized, per-table replication between PostgreSQL instances mimeo Replication and streams min_to_maxExtensionsExtensionsFunctions and aggregates · Extensionsaggregates and functions for PostgreSQL Server aggregates c custom-aggregates extension functions postgresql postgresql-extension min_to_max Functions and aggregates MinGWManual ChaptersPG18Platform-Specific NotesPostgreSQL 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 prerequisite packa… Minor VersionsManual ChaptersPG18C-Language FunctionsPostgreSQL 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 compile and work with any other m… Minor VersionsManual ChaptersPG18C-Language FunctionsPostgreSQL 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 minor release of the same majo… mipt-asjExtensionsExtensionsFull-text search · ExtensionsAbbreviation-aware approximate string joins using the Tao-Deng-Stonebraker algorithm approximate-join similarity string mipt_asj Full-text search MiscellaneousManual ChaptersPG18Building and Installation with Autoconf and MakeIt'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 advanced users. --with-pgpo… MiscellaneousManual ChaptersPG18Building and Installation with MesonDpgport=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 here then both server and c… MiscellaneousManual ChaptersPG18sepgsql — SELinux-, label-based mandatory access control (MAC) security moduleWe reject the LOAD command across the board, because any module loaded could easily circumvent security policy enforcement. Miscellaneous Coding ConventionsManual ChaptersPG18C Standard # Code in PostgreSQL should only rely on language features available in the C99 standard. That means a conforming C99 compiler has to be able to compile postgres, at least aside f… Miscellaneous FunctionsManual ChaptersPG18As always, there are some functions that just don't fit anywhere. PQfreemem Frees memory allocated by libpq. void PQfreemem(void *ptr); Frees memory allocated by libpq, particularly PQescape… mmddyyyyExtensionsExtensionsTime series · ExtensionsEducational date type with B-tree, wildcard patterns, and GiST nearest-neighbor search date gist education pg-mm-dd-yyyy Time series mobilitydbExtensionsExtensionsGeospatial · Extensionsgeospatial trajectory data management & analysis platform mobilitydb Geospatial mobilitydb_datagenExtensionsExtensionsGeospatial · ExtensionsMobilityDB random data generator functions mobilitydb Geospatial moddatetimeExtensionsExtensionsFunctions and aggregates · Extensionsfunctions for tracking last modification time moddatetime Functions and aggregates moddatetime — Functions for Tracking Last Modification TimeManual ChaptersPG18spi — Server Programming Interface features/examplesmoddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table. To use, create … modeshape_bsonExtensionsExtensionsUtilities · ExtensionsPostgreSQL extension to convert ModeShape BSON to and from JSON bson json modeshape modeshape_bson Utilities Modifying ArraysManual ChaptersPG18ArraysAn 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: UPDATE sal_emp SET pay_… Modifying Composite TypesManual ChaptersPG18Composite TypesHere are some examples of the proper syntax for inserting and updating composite columns. First, inserting or updating a whole column: INSERT INTO mytab (complex_col) VALUES((1.1,2.2)); UPDA… Modifying TablesManual ChaptersPG18When 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 not a convenient optio… mods_since_analyzeExtensionsExtensionsMonitoring · ExtensionsEstimation of number of changed tuples analyze changed tuples estimation mods_since_analyze Monitoring mogipixExtensionsExtensionsGeospatial · ExtensionsPostgreSQL extension exposing HEALPix and MOC Rust functions to SQL. astronomy astrophysics healpix moc pgrx mogipix Geospatial molecular_formulasExtensionsExtensionsData types · ExtensionsMolecular-formula parser and strongly typed PostgreSQL data type chemistry molecular-formula pgrx emi_monorepo Data types Monetary TypesManual ChaptersPG18The 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. The range shown in the… monetdb_fdwExtensionsExtensionsForeign data wrappers · ExtensionsMonetDB foreign data wrapper fdw monetdb monetdb_fdw Foreign data wrappers mongo_fdwExtensionsExtensionsForeign data wrappers · Extensionsforeign data wrapper for MongoDB access mongo_fdw Foreign data wrappers MonitoringManual ChaptersPG18Because logical replication is based on a similar architecture as physical streaming replication, the monitoring on a publication node is similar to monitoring of a physical replication prim… MonitoringManual ChaptersPG18Release 18Increase the logging granularity of server variable log_connections (Melanie Plageman) § This server variable was previously only boolean, which is still supported. Add log_connections optio… MonitoringManual ChaptersPG18Log-Shipping Standby ServersAn 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 lag by comparing the … Monitoring Database ActivityManual ChaptersPG18A database administrator frequently wonders, “What is the system doing right now?” This chapter discusses how to find that out. Several tools are available for monitoring database activity a… Monitoring Disk UsageManual ChaptersPG18This section discusses how to monitor the disk usage of a PostgreSQL database system.