Search PostgreSQL documentation

osm_fdwExtensionsExtensionsForeign data wrappers · ExtensionsForeign data wrapper for reading OpenStreetMap PBF files fdw osm pbf osm_fdw Foreign data wrappers osrmintExtensionsExtensionsGeospatial · ExtensionsPostgreSQL route OSRM integration osrmint Geospatial oss_fdwExtensionsExtensionsForeign data wrappers · ExtensionsAlibaba Cloud OSS foreign data wrapper for reading and writing OSS objects from PostgreSQL-compatible managed services. alibaba-cloud fdw object-storage oss oss_fdw Foreign data wrappers Other Database ObjectsManual ChaptersPG18Tables are the central objects in a relational database structure, because they hold your data. But they are not the only objects that exist in a database. Many other kinds of objects can be… Other DefaultsManual ChaptersPG18Client Connection Defaultsdynamic_library_path (string) # If a dynamically loadable module needs to be opened and the file name specified in the CREATE FUNCTION or LOAD command does not have a directory component (i.… Other Planner OptionsManual ChaptersPG18Query Planningdefault_statistics_target (integer) Sets the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS. Larger values increase the time … Other TargetsManual ChaptersPG18Building and Installation with Mesonclean # Remove all build products test # Run all enabled tests (including contrib) world # Build everything, including documentation help # List important targets Other Things to Watch ForManual ChaptersPG18Porting from Oracle PL/SQLThis section explains a few other things to watch for when porting Oracle PL/SQL functions to PostgreSQL. otletExtensionsExtensionsAI and vectors · ExtensionsLocal LLM inference inside PostgreSQL with background workers, queued tasks, receipts, and semantic data operations. pgrx rust otlet_pg AI and vectors otpExtensionsExtensionsSecurity · ExtensionsImplementation of TOTP compatible with Google Authenticator otp Security Out-of-Line, In-Memory TOAST StorageManual ChaptersPG18TOASTTOAST pointers can point to data that is not on disk, but is elsewhere in the memory of the current server process. Such pointers obviously cannot be long-lived, but they are nonetheless use… Out-of-Line, On-Disk TOAST StorageManual ChaptersPG18TOASTIf any of the columns of a table are TOAST-able, the table will have an associated TOAST table, whose OID is stored in the table's pg_class.reltoastrelid entry. On-disk TOASTed values are ke… Output ModesManual ChaptersPG18Logical Decoding Output PluginsOutput plugin callbacks can pass data to the consumer in nearly arbitrary formats. For some use cases, like viewing the changes via SQL, returning data in a data type that can contain arbitr… Output Plugin CallbacksManual ChaptersPG18Logical Decoding Output PluginsAn output plugin gets notified about changes that are happening via various callbacks it needs to provide. Concurrent transactions are decoded in commit order, and only changes belonging to … Output PluginsManual ChaptersPG18Logical Decoding Conceptstransform the data from the write-ahead log's internal representation into the format the consumer of a replication slot desires. OutputsManual ChaptersPG18ANALYZEWhen VERBOSE is specified, ANALYZE emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well. OutputsManual ChaptersPG18COPYOn successful completion, a COPY command returns a command tag of the form COPY count The count is the number of rows copied. Note psql will print this command tag only if the command was no… OutputsManual ChaptersPG18DELETEOn successful completion, a DELETE command returns a command tag of the form DELETE count The count is the number of rows deleted. Note that the number may be less than the number of rows th… OutputsManual ChaptersPG18EXECUTEThe command tag returned by EXECUTE is that of the prepared statement, and not EXECUTE. OutputsManual ChaptersPG18EXPLAINThe command's result is a textual description of the plan selected for the statement, optionally annotated with execution statistics. Section 14.1 describes the information provided. OutputsManual ChaptersPG18FETCHOn successful completion, a FETCH command returns a command tag of the form FETCH count The count is the number of rows fetched (possibly zero). Note that in psql, the command tag will not a… OutputsManual ChaptersPG18INSERTOn successful completion, an INSERT command returns a command tag of the form INSERT oid count The count is the number of rows inserted or updated. oid is always 0 (it used to be the OID ass… OutputsManual ChaptersPG18MERGEOn successful completion, a MERGE command returns a command tag of the form MERGE total_count The total_count is the total number of rows changed (whether inserted, updated, or deleted). If … OutputsManual ChaptersPG18MOVEOn successful completion, a MOVE command returns a command tag of the form MOVE count The count is the number of rows that a FETCH command with the same parameters would have returned (possi… OutputsManual ChaptersPG18UPDATEOn successful completion, an UPDATE command returns a command tag of the form UPDATE count The count is the number of rows updated, including matched rows whose values did not change. Note t… OutputsManual ChaptersPG18VACUUMWhen VERBOSE is specified, VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well. OverloadingManual ChaptersPG18CREATE FUNCTIONPostgreSQL allows function overloading; that is, the same name can be used for several different functions so long as they have distinct input argument types. Whether or not you use it, this… OverviewManual ChaptersPG18Table 52.1 lists the system catalogs. More detailed documentation of each catalog follows below. Most system catalogs are copied from the template database during database creation and are t… OverviewManual ChaptersPG18Table PartitioningPartitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits: Query performance can be improved dramatically in … OverviewManual ChaptersPG18Using Host VariablesPassing data between the C program and the SQL statements is particularly simple in embedded SQL. Instead of having the program paste the data into the statement, which entails various compl… OverviewManual ChaptersPG18Hash IndexesPostgreSQL includes an implementation of persistent on-disk hash indexes, which are fully crash recoverable. Any data type can be indexed by a hash index, including data types that do not ha… OverviewManual ChaptersPG18Locale SupportLocale support is automatically initialized when a database cluster is created using initdb. initdb will initialize the database cluster with the locale setting of its execution environment … OverviewManual ChaptersPG18Synchronous Replication Support for Logical DecodingLogical decoding can be used to build synchronous replication solutions with the same user interface as synchronous replication for streaming replication. To do this, the streaming replicati… OverviewManual ChaptersPG18A small number of objects, like role, database, and tablespace names, are defined at the cluster level and stored in the pg_global tablespace. Inside the cluster are multiple databases, whic… OverviewManual ChaptersPG18pgrowlocks — show a table's row locking informationpgrowlocks(text) returns setof record The parameter is the name of a table. The result is a set of records, with one row for each locked row within the table. The output columns are shown in… OverviewManual ChaptersPG18PL/pgSQL is a loadable procedural language for the PostgreSQL database system. The design goals of PL/pgSQL were to create a loadable procedural language that can be used to create functions… OverviewManual ChaptersPG18PL/Tcl offers most of the capabilities a function writer has in the C language, with a few restrictions, and with the addition of the powerful string processing libraries that are available … OverviewManual ChaptersPG18The protocol has separate phases for startup and normal operation. In the startup phase, the frontend opens a connection to the server and authenticates itself to the satisfaction of the ser… OverviewManual ChaptersPG18The process of retrieving or the command to retrieve data from a database is called a query. In SQL the SELECT command is used to specify queries. The general syntax of the SELECT command is… OverviewManual ChaptersPG18Release 18PostgreSQL 18 contains many new features and enhancements, including: An asynchronous I/O (AIO) subsystem that can improve performance of sequential scans, bitmap heap scans, vacuums, and ot…