Search PostgreSQL documentation

Connection Management OptionsManual ChaptersPG18F.38.1.10. Connection Management Options By default, all connections that postgres_fdw establishes to foreign servers are kept open in the local session for re-use. keep_connections (boolean…Connection Management Options Connection OptionsManual ChaptersPG18F.38.1.1. Connection Options A foreign server using the postgres_fdw foreign data wrapper can have the same options that libpq accepts in connection strings, as described in Section 32.1.2, …Connection Options Connection SettingsManual ChaptersPG1819.3.1. Connection Settings listen_addresses (string) Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of …Connection Settings Connection Status FunctionsManual ChaptersPG1832.2. Connection Status Functions These functions can be used to interrogate the status of an existing database connection object. Tip libpq application programmers should be careful to main…Connection Status Functions Connection StringsManual ChaptersPG1832.1.1. Connection Strings Several libpq functions parse a user-specified string to obtain connection parameters. There are two accepted formats for these strings: plain keyword/value string…Connection Strings Connection URIsManual ChaptersPG1832.1.1.2. Connection URIs The general form for a connection URI is: postgresql://[userspec@][hostspec][/dbname][?paramspec] where userspec is: user[:password] and hostspec is: [host][:port][…Connection URIs Connections and AuthenticationManual ChaptersPG1819.3. Connections and AuthenticationConnections and Authentication ConstantsManual ChaptersPG184.1.2. Constants There are three kinds of implicitly-typed constants in PostgreSQL: strings, bit strings, and numbers. Constants can also be specified with explicit types, which can enable m…Constants Constants of Other TypesManual ChaptersPG184.1.2.7. Constants of Other Types A constant of an arbitrary type can be entered using any one of the following notations: type 'string' 'string'::type CAST ( 'string' AS type ) The string c…Constants of Other Types Constraint NamingManual ChaptersPG18Constraint Naming The SQL standard says that table and domain constraints must have names that are unique across the schema containing the table or domain. PostgreSQL is laxer: it only requi…Constraint Naming constraint_column_usageManual ChaptersPG1835.18. constraint_column_usage The view constraint_column_usage identifies all columns in the current database that are used by some constraint. Only those columns are shown that are contain…constraint_column_usage constraint_table_usageManual ChaptersPG1835.19. constraint_table_usage The view constraint_table_usage identifies all tables in the current database that are used by some constraint and are owned by a currently enabled role. (This …constraint_table_usage ConstraintsManual ChaptersPG185.5. Constraints Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. For example, a colum…Constraints ConstraintsManual ChaptersPG18E.6.3.2.1. Constraints Allow the specification of non-overlapping PRIMARY KEY, UNIQUE, and foreign key constraints (Paul A. Jungwirth) § § This is specified by WITHOUT OVERLAPS for PRIMARY K…Constraints Constraints on RangesManual ChaptersPG188.17.10. Constraints on Ranges While UNIQUE is a natural constraint for scalar values, it is usually unsuitable for range types. Instead, an exclusion constraint is often more appropriate (s…Constraints on Ranges Constructing Composite ValuesManual ChaptersPG188.16.2. Constructing Composite Values To write a composite value as a literal constant, enclose the field values within parentheses and separate them by commas. You can put double quotes aro…Constructing Composite Values Constructing Ranges and MultirangesManual ChaptersPG188.17.6. Constructing Ranges and Multiranges Each range type has a constructor function with the same name as the range type. Using the constructor function is frequently more convenient than…Constructing Ranges and Multiranges Container TypesManual ChaptersPG1836.2.2. Container Types PostgreSQL has three kinds of “container” types, which are types that contain multiple values of other types. These are arrays, composites, and ranges. Arrays can hol…Container Types CONTINUEManual ChaptersPG1841.6.5.3. CONTINUE CONTINUE [ label ] [ WHEN boolean-expression ]; If no label is given, the next iteration of the innermost loop is begun. That is, all statements remaining in the loop body…CONTINUE Continuous Archiving and Point-in-Time Recovery (PITR)Manual ChaptersPG1825.3. Continuous Archiving and Point-in-Time Recovery (PITR) At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_wal/ subdirectory of the cluster's data directory. The log r…Continuous Archiving and Point-in-Time Recovery (PITR) Continuous Archiving in StandbyManual ChaptersPG1826.2.9. Continuous Archiving in Standby When continuous WAL archiving is used in a standby, there are two different scenarios: the WAL archive can be shared between the primary and the stand…Continuous Archiving in Standby Control Data FunctionsManual ChaptersPG189.27.10. Control Data Functions The functions shown in Table 9.88 print information initialized during initdb, such as the catalog version. They also show information about write-ahead loggi…Control Data Functions Control FunctionsManual ChaptersPG1832.11. Control Functions These functions control miscellaneous details of libpq's behavior. PQclientEncoding # Returns the client encoding. int PQclientEncoding(const PGconn *conn); Note tha…Control Functions Control StructuresManual ChaptersPG1841.6. Control Structures Control structures are probably the most useful (and important) part of PL/pgSQL. With PL/pgSQL's control structures, you can manipulate PostgreSQL data in a very fl…Control Structures Controlled Object ClassesManual ChaptersPG18F.40.5.1. Controlled Object Classes The security model of SELinux describes all the access control rules as relationships between a subject entity (typically, a client of the database) and a…Controlled Object Classes Controlling Text SearchManual ChaptersPG1812.3. Controlling Text Search To implement full text searching there must be a function to create a tsvector from a document and a tsquery from a user query. Also, we need to return results …Controlling Text Search Controlling the Planner with Explicit JOIN ClausesManual ChaptersPG1814.3. Controlling the Planner with Explicit JOIN Clauses It is possible to control the query planner to some extent by using the explicit JOIN syntax. To see why this matters, we first need …Controlling the Planner with Explicit JOIN Clauses ConventionsManual ChaptersPG183. Conventions The following conventions are used in the synopsis of a command: brackets ([ and ]) indicate optional parts. Braces ({ and }) and vertical lines (|) indicate that you must cho…Conventions convert-crlfManual ChaptersPG18F.26.3.8.4. convert-crlf Whether to convert \n into \r\n when encrypting and \r\n to \n when decrypting. RFC 4880 specifies that text data should be stored using \r\n line-feeds. Use this to…convert-crlf Cooperation with ViewsManual ChaptersPG1839.4.2. Cooperation with Views A simple way to protect view relations from the mentioned possibility that someone can try to run INSERT, UPDATE, or DELETE on them is to let those query trees…Cooperation with Views COPYManual ChaptersPG18E.6.3.2.2. COPY Add REJECT_LIMIT to control the number of invalid rows COPY FROM can ignore (Atsushi Torikoshi) § This is available when ON_ERROR = 'ignore'. Allow COPY TO to copy rows from …COPY COPYManual ChaptersPG18COPY COPY — copy data between a file and a table Synopsis COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] [ WHERE …COPY COPY OperationsManual ChaptersPG1854.2.6. COPY Operations The COPY command allows high-speed bulk data transfer to or from the server. Copy-in and copy-out operations each switch the connection into a distinct sub-protocol, …COPY Operations COPY Progress ReportingManual ChaptersPG1827.4.3. COPY Progress Reporting Whenever COPY is running, the pg_stat_progress_copy view will contain one row for each backend that is currently running a COPY command. The table below descr…COPY Progress Reporting Copying CollationsManual ChaptersPG1823.2.2.3.3. Copying Collations The command CREATE COLLATION can also be used to create a new collation from an existing collation, which can be useful to be able to use operating-system-inde…Copying Collations Copying TypesManual ChaptersPG1841.3.3. Copying Types name table.column%TYPE name variable%TYPE %TYPE provides the data type of a table column or a previously-declared PL/pgSQL variable. You can use this to declare variabl…Copying Types Cost Estimation OptionsManual ChaptersPG18F.38.1.3. Cost Estimation Options postgres_fdw retrieves remote data by executing queries against remote servers, so ideally the estimated cost of scanning a foreign table should be whatever…Cost Estimation Options Cost-based Vacuum DelayManual ChaptersPG1819.10.2. Cost-based Vacuum Delay During the execution of VACUUM and ANALYZE commands, the system maintains an internal counter that keeps track of the estimated cost of the various I/O opera…Cost-based Vacuum Delay Coverage with Autoconf and MakeManual ChaptersPG1831.5.1. Coverage with Autoconf and Make A typical workflow looks like this: ./configure --enable-coverage ... OTHER OPTIONS ... make make check # or other test suite make coverage-html Then …Coverage with Autoconf and Make Coverage with MesonManual ChaptersPG1831.5.2. Coverage with Meson A typical workflow looks like this: meson setup -Db_coverage=true ... OTHER OPTIONS ... builddir/ meson compile -C builddir/ meson test -C builddir/ cd builddir/ …Coverage with Meson