Search PostgreSQL documentation

NotesManual ChaptersPG18Notes Specifying a savepoint name that was not previously defined is an error. It is not possible to release a savepoint when the transaction is in an aborted state; to do that, use ROLLBACK…Notes NotesManual ChaptersPG18Notes A user can only revoke privileges that were granted directly by that user. If, for example, user A has granted a privilege with grant option to user B, and user B has in turn granted i…Notes NotesManual ChaptersPG18Notes To roll back a prepared transaction, you must be either the same user that executed the transaction originally, or a superuser. But you do not have to be in the same session that execu…Notes NotesManual ChaptersPG18Notes Use RELEASE SAVEPOINT to destroy a savepoint without discarding the effects of commands executed after it was established. Specifying a savepoint name that has not been established is …Notes NotesManual ChaptersPG18Notes Use COMMIT to successfully terminate a transaction. Issuing ROLLBACK outside of a transaction block emits a warning and otherwise has no effect. ROLLBACK AND CHAIN outside of a transac…Notes NotesManual ChaptersPG18Notes Use ROLLBACK TO to rollback to a savepoint. Use RELEASE SAVEPOINT to destroy a savepoint, keeping the effects of commands executed after it was established. Savepoints can only be esta…Notes NotesManual ChaptersPG18Notes CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they int…Notes NotesManual ChaptersPG18Notes Because PostgreSQL does not require constraint names to be unique within a schema (but only per-table), it is possible that there is more than one match for a specified constraint name…Notes NotesManual ChaptersPG18Notes Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role has been granted memberships WITH INHERIT TRUE, it automatically has …Notes NotesManual ChaptersPG18Notes SET SESSION AUTHORIZATION cannot be used within a SECURITY DEFINER function.Notes NotesManual ChaptersPG18Notes If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it emits a warning and otherwise has no effect. It is possible to dispense with SET TRANSACTION by instead sp…Notes 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 NOTIFYSQL CommandsReferenceNOTIFY Sessions & Settings 10 generate a notification NOTIFY channel [ , payload ] The NOTIFY command sends a notification event together with an optional “ payload ” string to each client a…SQL Commands · Sessions & Settings notify_buffersConfigurationReferencenotify_buffers Resource Usage 17 Specifies the amount of shared memory to use to cache the contents of pg_notify (see Table 65.1). If this value is specified without units, it is taken as bl…Configuration · Resource Usage notify_nowExtensionsExtensionsPostgreSQL Extension to send multiple responses from one SQL Query using LISTEN/NOFITY API async response dynamic result set json listen multi result notify notify_now FeaturesFeatures · Extensions NotifyBufferWait EventsReferenceNotifyBuffer LWLock 10 Waiting for I/O on an async (notify) buffer. 11 12 13 Waiting for I/O on a NOTIFY message SLRU buffer. 14 15 16 17 18 19 20 9.6 A brief sample is normal around short i…Wait Events · LWLock NotifyChannelHashWait EventsReferenceNotifyChannelHash LWLock 19 Waiting to access the NOTIFY channel hash table. 20Wait Events · LWLock NotifyQueueWait EventsReferenceNotifyQueue LWLock 10 Waiting to read or update notification messages. 11 12 13 Waiting to read or update NOTIFY messages. 14 15 16 17 18 19 20 9.6 A brief sample is normal around short inte…Wait Events · LWLock NotifyQueueTailWait EventsReferenceNotifyQueueTail LWLock 10 Waiting to update limit on notification message storage. 11 12 13 Waiting to update limit on NOTIFY message storage. 14 15 16 17 18 19 20 9.6 A brief sample is norm…Wait Events · LWLock NotifySLRUWait EventsReferenceNotifySLRU LWLock 10 Waiting to read or update shared notification state. 11 12 13 Waiting to access the NOTIFY message SLRU cache. 14 15 16 17 18 19 20 9.6 A brief sample is normal around s…Wait Events · LWLock nowFunctionsReferencenow Date/Time Functions And Operators 10 Current date and time (start of current transaction); see Section 9.9.4 now ( ) → timestamp with time zone Current date and time (start of current tr…Functions · Date/Time Functions And Operators npmExtensionsExtensionsSchema and tables for tracking npm package metadata and daily download counts. npm FeaturesFeatures · Extensions npointsFunctionsReferencenpoints Geometric Functions And Operators 10 number of points npoints ( path ) → int number of points npoints ( polygon ) → int number of points 11 12 13 Returns the number of points. Availa…Functions · Geometric Functions And Operators nr_modelExtensionsExtensionsAI-powered Autonomous Data System aixdb database nr_model AI and vectorsAI and vectors · Extensions ntextExtensionsExtensionsJapanese normalized-comparison text type for UTF-8 strings ntext Data typesData types · Extensions nth_valueFunctionsReferencenth_value Window Functions 10 returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row nth_value ( value any, nth integer ) → same ty…Functions · Window Functions ntileFunctionsReferencentile Window Functions 10 integer ranging from 1 to the argument value, dividing the partition as equally as possible ntile ( num_buckets integer ) → integer integer ranging from 1 to the ar…Functions · Window Functions NULLSQL CommandsPG18NULL “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…CREATE TABLE › Compatibility › NULL “Constraint” 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”