Search PostgreSQL documentation

UsageManual ChaptersPG18F.10.3. Usage Table F.2 shows the specialized operators provided for type cube. Table F.2. Cube Operators Operator Description cube && cube → boolean Do the cubes overlap? cube @> cube → boo…Usage UsageManual ChaptersPG18F.12.2. Usage Installing the dict_int extension creates a text search template intdict_template and a dictionary intdict based on it, with the default parameters. You can alter the parameter…Usage UsageManual ChaptersPG18F.13.2. Usage Installing the dict_xsyn extension creates a text search template xsyn_template and a dictionary xsyn based on it, with default parameters. You can alter the parameters, for ex…Usage UsageManual ChaptersPG18UsageUsage UsageManual ChaptersPG18Usage These are the steps to perform an upgrade with pg_upgrade: Note The steps to upgrade logical replication clusters are not covered here; refer to Section 29.13 for details. Optionally m…Usage UsageManual ChaptersPG18F.39.4. Usage The seg module includes a GiST index operator class for seg values. The operators supported by the GiST operator class are shown in Table F.31. Table F.31. Seg GiST Operators O…Usage UsageManual ChaptersPG18F.48.2. Usage Installing the unaccent extension creates a text search template unaccent and a dictionary unaccent based on it. The unaccent dictionary has the default parameter setting RULES…Usage Usage PatternsManual ChaptersPG185.10.6. Usage Patterns Schemas can be used to organize your data in many ways. A secure schema usage pattern prevents untrusted users from changing the behavior of other users' queries. When…Usage Patterns usage_privilegesManual ChaptersPG1835.59. usage_privileges The view usage_privileges identifies USAGE privileges granted on various kinds of objects to a currently enabled role or by a currently enabled role. In PostgreSQL, t…usage_privileges Use COPYManual ChaptersPG1814.4.2. Use COPY Use COPY to load all the rows in one command, instead of using a series of INSERT commands. The COPY command is optimized for loading large numbers of rows; it is less flexi…Use COPY Use of Secondary File SystemsManual ChaptersPG1818.2.1. Use of Secondary File Systems Many installations create their database clusters on file systems (volumes) other than the machine's “root” volume. If you choose to do this, it is not …Use of Secondary File Systems User Name MapsManual ChaptersPG1820.2. User Name Maps When using an external authentication system such as Ident or GSSAPI, the name of the operating system user that initiated the connection might not be the same as the da…User Name Maps User's OverviewManual ChaptersPG1826.4.1. User's Overview When the hot_standby parameter is set to true on a standby server, it will begin accepting connections once the recovery has brought the system to a consistent state …User's Overview User-Defined AggregatesManual ChaptersPG1836.12. User-Defined Aggregates Aggregate functions in PostgreSQL are defined in terms of state values and state transition functions. That is, an aggregate operates using a state value that …User-Defined Aggregates User-Defined Base TypesManual ChaptersPG1834.4.5.3. User-Defined Base Types New user-defined base types are not directly supported by ECPG. You can use the external string representation and host variables of type char[] or VARCHAR[…User-Defined Base Types User-Defined FunctionsManual ChaptersPG1836.3. User-Defined Functions PostgreSQL provides four kinds of functions: query language functions (functions written in SQL) (Section 36.5) procedural language functions (functions written …User-Defined Functions User-Defined OperatorsManual ChaptersPG1836.14. User-Defined Operators Every operator is “syntactic sugar” for a call to an underlying function that does the real work; so you must first create the underlying function before you ca…User-Defined Operators User-Defined ProceduresManual ChaptersPG1836.4. User-Defined Procedures A procedure is a database object similar to a function. The key differences are: Procedures are defined with the CREATE PROCEDURE command, not CREATE FUNCTION. …User-Defined Procedures User-Defined TypesManual ChaptersPG1836.13. User-Defined Types As described in Section 36.2, PostgreSQL can be extended to support new data types. This section describes how to define new base types, which are data types define…User-Defined Types user_defined_typesManual ChaptersPG1835.60. user_defined_types The view user_defined_types currently contains all composite types defined in the current database. Only those types are shown that the current user has access to (…user_defined_types user_mapping_optionsManual ChaptersPG1835.61. user_mapping_options The view user_mapping_options contains all the options defined for user mappings in the current database. Only those user mappings are shown where the current use…user_mapping_options user_mappingsManual ChaptersPG1835.62. user_mappings The view user_mappings contains all user mappings defined in the current database. Only those user mappings are shown where the current user has access to the correspond…user_mappings Using amcheck EffectivelyManual ChaptersPG18F.1.3. Using amcheck Effectively amcheck can be effective at detecting various types of failure modes that data checksums will fail to catch. These include: Structural inconsistencies caused…Using amcheck Effectively Using C++ for ExtensibilityManual ChaptersPG1836.10.16. Using C++ for Extensibility Although the PostgreSQL backend is written in C, it is possible to write extensions in C++ if these guidelines are followed: All functions accessed by t…Using C++ for Extensibility Using Client CertificatesManual ChaptersPG1818.9.3. Using Client Certificates To require the client to supply a trusted certificate, place certificates of the root certificate authorities (CAs) you trust in a file in the data director…Using Client Certificates Using Composite Types in QueriesManual ChaptersPG188.16.5. Using Composite Types in Queries There are various special syntax rules and behaviors associated with composite types in queries. These rules provide useful shortcuts, but can be con…Using Composite Types in Queries Using CSV-Format Log OutputManual ChaptersPG1819.8.4. Using CSV-Format Log Output Including csvlog in the log_destination list provides a convenient way to import log files into a database table. This option emits log lines in comma-sep…Using CSV-Format Log Output Using CursorsManual ChaptersPG1834.3.2. Using Cursors To retrieve a result set holding multiple rows, an application has to declare a cursor and fetch each row from the cursor. The steps to use a cursor are the following: …Using Cursors Using CursorsManual ChaptersPG1841.7.3. Using Cursors Once a cursor has been opened, it can be manipulated with the statements described here. These manipulations need not occur in the same function that opened the cursor …Using Cursors Using Descriptor AreasManual ChaptersPG1834.7. Using Descriptor Areas An SQL descriptor area is a more sophisticated method for processing the result of a SELECT, FETCH or a DESCRIBE statement. An SQL descriptor area groups the dat…Using Descriptor Areas Using DTrace for Tracing PostgreSQLManual ChaptersPG1817.7.4.4. Using DTrace for Tracing PostgreSQL Yes, using DTrace is possible. See Section 27.5 for further information. If you see the linking of the postgres executable abort with an error m…Using DTrace for Tracing PostgreSQL Using EXPLAINManual ChaptersPG1814.1. Using EXPLAIN PostgreSQL devises a query plan for each query it receives. Choosing the right plan to match the query structure and the properties of the data is absolutely critical for…Using EXPLAIN Using Host VariablesManual ChaptersPG1834.4. Using Host Variables In Section 34.3 you saw how you can execute SQL statements from an embedded SQL program. Some of those statements only used fixed values and did not provide a way …Using Host Variables Using JSON-Format Log OutputManual ChaptersPG1819.8.5. Using JSON-Format Log Output Including jsonlog in the log_destination list provides a convenient way to import log files into many different programs. This option emits log lines in …Using JSON-Format Log Output Using Mixed NotationManual ChaptersPG184.3.3. Using Mixed Notation The mixed notation combines positional and named notation. However, as already mentioned, named arguments cannot precede positional arguments. For example: SELECT…Using Mixed Notation Using Named NotationManual ChaptersPG184.3.2. Using Named Notation In named notation, each argument's name is specified using => to separate it from the argument expression. For example: SELECT concat_lower_or_upper(a => 'Hello',…Using Named Notation Using pg_dumpallManual ChaptersPG1825.1.2. Using pg_dumpall pg_dump dumps only a single database at a time, and it does not dump information about roles or tablespaces (because those are cluster-wide rather than per-database)…Using pg_dumpall Using Pipeline ModeManual ChaptersPG1832.5.1. Using Pipeline Mode To issue pipelines, the application must switch the connection into pipeline mode, which is done with PQenterPipelineMode. PQpipelineStatus can be used to test wh…Using Pipeline Mode Using Positional NotationManual ChaptersPG184.3.1. Using Positional Notation Positional notation is the traditional mechanism for passing arguments to functions in PostgreSQL. An example is: SELECT concat_lower_or_upper('Hello', 'Worl…Using Positional Notation Using ProbesManual ChaptersPG1827.5.3. Using Probes The example below shows a DTrace script for analyzing transaction counts in the system, as an alternative to snapshotting pg_stat_database before and after a performance…Using Probes