Search PostgreSQL documentation

NotesManual ChaptersPG18Notes The function set_config provides equivalent functionality; see Section 9.28.1. Also, it is possible to UPDATE the pg_settings system view to perform the equivalent of SET.Notes NotesManual ChaptersPG18Notes The function current_setting produces equivalent output; see Section 9.28.1. Also, the pg_settings system view produces the same information.Notes NotesManual ChaptersPG18Notes You must have the TRUNCATE privilege on a table to truncate it. TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on…Notes NotesManual ChaptersPG18Notes You can unlisten something you were not listening for; no warning or error will appear. At the end of each session, UNLISTEN * is automatically executed. A transaction that has execute…Notes NotesManual ChaptersPG18Notes When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an …Notes NotesManual ChaptersPG18Notes To vacuum a table, one must ordinarily have the MAINTAIN privilege on the table. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. V…Notes NotesManual ChaptersPG18Notes VALUES lists with very large numbers of rows should be avoided, as you might encounter out-of-memory failures or poor performance. VALUES appearing within INSERT is a special case (bec…Notes NotesManual ChaptersPG18Notes vacuumlo works by the following method: First, vacuumlo builds a temporary table which contains all of the OIDs of the large objects in the selected database. It then scans through all…Notes Notes for Windows UsersManual ChaptersPG18Notes for Windows Users psql is built as a “console application”. Since the Windows console windows use a different encoding than the rest of the system, you must take special care when usin…Notes for Windows Users Notice ProcessingManual ChaptersPG1832.13. Notice Processing Notice and warning messages generated by the server are not returned by the query execution functions, since they do not imply failure of the query. Instead they are…Notice Processing NOTIFYManual ChaptersPG18NOTIFY NOTIFY — generate a notification Synopsis NOTIFY channel [ , payload ]NOTIFY NULL HandlingManual ChaptersPG18F.26.8.2. NULL Handling As is standard in SQL, all functions return NULL, if any of the arguments are NULL. This may create security risks on careless usage.NULL Handling NULL “Constraint”Manual ChaptersPG18NULL “Constraint” The NULL “constraint” (actually a non-constraint) is a PostgreSQL extension to the SQL standard that is included for compatibility with some other database systems (and for…NULL “Constraint” Null, NoneManual ChaptersPG1844.2.2. Null, None If an SQL null value is passed to a function, the argument value will appear as None in Python. For example, the function definition of pymax shown in Section 44.1 will re…Null, None NULLIFManual ChaptersPG189.18.3. NULLIF NULLIF(value1, value2) The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of the …NULLIF Numeric ConstantsManual ChaptersPG184.1.2.6. Numeric Constants Numeric constants are accepted in these general forms: digits digits.[digits][e[+-]digits] [digits].digits[e[+-]digits] digitse[+-]digits where digits is one or mo…Numeric Constants Numeric TypesManual ChaptersPG188.1. Numeric Types Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals. Table 8.2 lists the availabl…Numeric Types numeric, decimalManual ChaptersPG1834.4.4.2.3. numeric, decimal The handling of the numeric and decimal types is similar to the interval type: It requires defining a pointer, allocating some memory space on the heap, and acce…numeric, decimal OAuth Authorization/AuthenticationManual ChaptersPG1820.15. OAuth Authorization/Authentication OAuth 2.0 is an industry-standard framework, defined in RFC 6749, to enable third-party applications to obtain limited access to a protected resourc…OAuth Authorization/Authentication OAuth SupportManual ChaptersPG1832.20. OAuth Support libpq implements support for the OAuth v2 Device Authorization client flow, documented in RFC 8628, as an optional module. See the installation documentation for informa…OAuth Support OAuth Validator CallbacksManual ChaptersPG1850.3. OAuth Validator Callbacks OAuth validator modules implement their functionality by defining a set of callbacks. The server will call them as required to process the authentication requ…OAuth Validator Callbacks OAuth Validator ModulesManual ChaptersPG18Chapter 50. OAuth Validator Modules PostgreSQL provides infrastructure for creating custom modules to perform server-side validation of OAuth bearer tokens. Because OAuth implementations var…OAuth Validator Modules OAUTHBEARER AuthenticationManual ChaptersPG1854.3.2. OAUTHBEARER Authentication OAUTHBEARER is a token-based mechanism for federated authentication. It is described in detail in RFC 7628. A typical exchange differs depending on whether…OAUTHBEARER Authentication Object Identifier TypesManual ChaptersPG188.19. Object Identifier Types Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. Type oid represents an object identifier. There are also …Object Identifier Types Object Information and Addressing FunctionsManual ChaptersPG189.27.5. Object Information and Addressing Functions Table 9.81 lists functions related to database object identification and addressing. Table 9.81. Object Information and Addressing Functio…Object Information and Addressing Functions Object Name OptionsManual ChaptersPG18F.38.1.2. Object Name Options These options can be used to control the names used in SQL statements sent to the remote PostgreSQL server. These options are needed when a foreign table is cre…Object Name Options Obsolete Functions for COPYManual ChaptersPG1832.10.3. Obsolete Functions for COPY These functions represent older methods of handling COPY. Although they still work, they are deprecated due to poor error handling, inconvenient methods …Obsolete Functions for COPY Obsolete Functions for Sending Cancel RequestsManual ChaptersPG1832.7.2. Obsolete Functions for Sending Cancel Requests These functions represent older methods of sending cancel requests. Although they still work, they are deprecated due to not sending th…Obsolete Functions for Sending Cancel Requests Obsolete or Renamed FeaturesManual ChaptersPG18Appendix O. Obsolete or Renamed Features Functionality is sometimes removed from PostgreSQL, feature, setting and file names sometimes change, or documentation moves to different places. Thi…Obsolete or Renamed Features Obtaining Execution Location InformationManual ChaptersPG1841.6.9. Obtaining Execution Location Information The GET DIAGNOSTICS command, previously described in Section 41.5.5, retrieves information about current execution state (whereas the GET STA…Obtaining Execution Location Information Obtaining Information about an ErrorManual ChaptersPG1841.6.8.1. Obtaining Information about an Error Exception handlers frequently need to identify the specific error that occurred. There are two ways to get information about the current except…Obtaining Information about an Error Obtaining the Result StatusManual ChaptersPG1841.5.5. Obtaining the Result Status There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form: GET [ CURRENT ] D…Obtaining the Result Status Obtaining the Seek Position of a Large ObjectManual ChaptersPG1833.3.8. Obtaining the Seek Position of a Large Object To obtain the current read or write location of a large object descriptor, call int lo_tell(PGconn *conn, int fd); If there is an error,…Obtaining the Seek Position of a Large Object Off-line Enabling of ChecksumsManual ChaptersPG1828.2.1. Off-line Enabling of Checksums The pg_checksums application can be used to enable or disable data checksums, as well as verify checksums, on an offline cluster.Off-line Enabling of Checksums OID AssignmentManual ChaptersPG1868.2.2. OID Assignment A catalog row appearing in the initial data can be given a manually-assigned OID by writing an oid => nnnn metadata field. Furthermore, if an OID is assigned, a C macr…OID Assignment OID Reference LookupManual ChaptersPG1868.2.3. OID Reference Lookup In principle, cross-references from one initial catalog row to another could be written just by writing the preassigned OID of the referenced row in the referenc…OID Reference Lookup oid2nameManual ChaptersPG18oid2name oid2name — resolve OIDs and file nodes in a PostgreSQL data directory Synopsis oid2name [option...]oid2name Omitted FROM ClausesManual ChaptersPG18Omitted FROM Clauses PostgreSQL allows one to omit the FROM clause. It has a straightforward use to compute the results of simple expressions: SELECT 2+2; ?column? ---------- 4 Some other SQ…Omitted FROM Clauses Omitting Sub-SELECT Aliases in FROMManual ChaptersPG18Omitting Sub-SELECT Aliases in FROM According to the SQL standard, a sub-SELECT in the FROM list must have an alias. In PostgreSQL, this alias may be omitted.Omitting Sub-SELECT Aliases in FROM Omitting the AS Key WordManual ChaptersPG18Omitting the AS Key Word In the SQL standard, the optional key word AS can be omitted before an output column name whenever the new column name is a valid column name (that is, not the same …Omitting the AS Key Word