Search PostgreSQL documentation

NotesManual ChaptersPG18seg — a datatype for line segments or floating point intervalsFor examples of usage, see the regression test sql/seg.sql. The mechanism that converts (+-) to regular ranges isn't completely accurate in determining the number of significant digits for t… NotesManual ChaptersPG18SPI_cursor_fetchFetching backward may fail if the cursor's plan was not created with the CURSOR_OPT_SCROLL option. NotesManual ChaptersPG18SPI_cursor_findBeware that this function can return a Portal object that does not have cursor-like properties; for example it might not return tuples. If you simply pass the Portal pointer to other SPI fun… NotesManual ChaptersPG18SPI_cursor_moveMoving backward may fail if the cursor's plan was not created with the CURSOR_OPT_SCROLL option. NotesManual ChaptersPG18SPI_executeAll SPI query-execution functions set both SPI_processed and SPI_tuptable (just the pointer, not the contents of the structure). Save these two global variables into local C function variabl… NotesManual ChaptersPG18SPI_keepplanThe passed-in statement is relocated to permanent storage by means of pointer adjustment (no data copying is required). If you later wish to delete it, use SPI_freeplan on it. NotesManual ChaptersPG18SPI_prepare_cursorUseful bits to set in cursorOptions include CURSOR_OPT_SCROLL, CURSOR_OPT_NO_SCROLL, CURSOR_OPT_FAST_PLAN, CURSOR_OPT_GENERIC_PLAN, and CURSOR_OPT_CUSTOM_PLAN. Note in particular that CURSOR… NotesManual ChaptersPG18SPI_prepareIf no parameters are defined, a generic plan will be created at the first use of SPI_execute_plan, and used for all subsequent executions as well. If there are parameters, the first few uses… NotesManual ChaptersPG18SPI_saveplanThe originally passed-in statement is not freed, so you might wish to do SPI_freeplan on it to avoid leaking memory until SPI_finish. In most cases, SPI_keepplan is preferred to this functio… NotesManual ChaptersPG18SPI_scroll_cursor_fetchSee the SQL FETCH command for details of the interpretation of the direction and count parameters. Direction values other than FETCH_FORWARD may fail if the cursor's plan was not created wit… NotesManual ChaptersPG18SPI_scroll_cursor_moveSee the SQL FETCH command for details of the interpretation of the direction and count parameters. Direction values other than FETCH_FORWARD may fail if the cursor's plan was not created wit… NotesManual ChaptersPG18ABORTUse COMMIT to successfully terminate a transaction. Issuing ABORT outside of a transaction block emits a warning and otherwise has no effect. NotesManual ChaptersPG18ALTER AGGREGATEThe recommended syntax for referencing an ordered-set aggregate is to write ORDER BY between the direct and aggregated argument specifications, in the same style as in CREATE AGGREGATE. Howe… NotesManual ChaptersPG18ALTER COLLATIONWhen a collation object is created, the provider-specific version of the collation is recorded in the system catalog. When the collation is used, the current version is checked against the r… NotesManual ChaptersPG18ALTER DATABASEIt is also possible to tie a session default to a specific role rather than to a database; see ALTER ROLE. Role-specific settings override database-specific ones if there is a conflict. NotesManual ChaptersPG18ALTER DEFAULT PRIVILEGESUse psql's \ddp command to obtain information about existing assignments of default privileges. The meaning of the privilege display is the same as explained for \dp in Section 5.8. If you w… NotesManual ChaptersPG18ALTER DOMAINAlthough ALTER DOMAIN ADD CONSTRAINT attempts to verify that existing stored data satisfies the new constraint, this check is not bulletproof, because the command cannot “see” table rows tha… NotesManual ChaptersPG18ALTER FOREIGN TABLEThe key word COLUMN is noise and can be omitted. Consistency with the foreign server is not checked when a column is added or removed with ADD COLUMN or DROP COLUMN, a NOT NULL or CHECK cons… NotesManual ChaptersPG18ALTER INDEXThese operations are also possible using ALTER TABLE. ALTER INDEX is in fact just an alias for the forms of ALTER TABLE that apply to indexes. There was formerly an ALTER INDEX OWNER variant… NotesManual ChaptersPG18ALTER OPERATORRefer to Section 36.14 and Section 36.15 for further information. Since commutators come in pairs that are commutators of each other, ALTER OPERATOR SET COMMUTATOR will also set the commutat… NotesManual ChaptersPG18ALTER OPERATOR FAMILYNotice that the DROP syntax only specifies the “slot” in the operator family, by strategy or support number and input data type(s). The name of the operator or function occupying the slot is… NotesManual ChaptersPG18ALTER ROLEUse CREATE ROLE to add new roles, and DROP ROLE to remove a role. ALTER ROLE cannot change a role's memberships. Use GRANT and REVOKE to do that. Caution must be exercised when specifying an… NotesManual ChaptersPG18ALTER SEQUENCEALTER SEQUENCE will not immediately affect nextval results in backends, other than the current one, that have preallocated (cached) sequence values. They will use up all cached values prior … NotesManual ChaptersPG18ALTER SYSTEMThis command can't be used to set data_directory, allow_alter_system, nor parameters that are not allowed in postgresql.conf (e.g., preset options). See Section 19.1 for other ways to set th… NotesManual ChaptersPG18ALTER TABLEThe key word COLUMN is noise and can be omitted. When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default value is evaluated at the time of the statement a… NotesManual ChaptersPG18ALTER TRIGGERThe ability to temporarily enable or disable a trigger is provided by ALTER TABLE, not by ALTER TRIGGER, because ALTER TRIGGER has no convenient way to express the option of enabling or disa… NotesManual ChaptersPG18ALTER TYPEIf ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committ… NotesManual ChaptersPG18ALTER VIEWFor historical reasons, ALTER TABLE can be used with views too; but the only variants of ALTER TABLE that are allowed with views are equivalent to the ones shown above. NotesManual ChaptersPG18ANALYZETo analyze a table, one must ordinarily have the MAINTAIN privilege on the table. However, database owners are allowed to analyze all tables in their databases, except shared catalogs. ANALY… NotesManual ChaptersPG18BEGINSTART TRANSACTION has the same functionality as BEGIN. Use COMMIT or ROLLBACK to terminate a transaction block. Issuing BEGIN when already inside a transaction block will provoke a warning m… NotesManual ChaptersPG18CALLThe user must have EXECUTE privilege on the procedure in order to be allowed to invoke it. To call a function (not a procedure), use SELECT instead. If CALL is executed in a transaction bloc… NotesManual ChaptersPG18CLOSEPostgreSQL does not have an explicit OPEN cursor statement; a cursor is considered open when it is declared. Use the DECLARE statement to declare a cursor. You can see all available cursors … NotesManual ChaptersPG18CLUSTERTo cluster a table, one must have the MAINTAIN privilege on the table. In cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unim… NotesManual ChaptersPG18COMMENTThere is presently no security mechanism for viewing comments: any user connected to a database can see all the comments for objects in that database. For shared objects such as databases, r… NotesManual ChaptersPG18COMMIT PREPAREDTo commit 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 executed the t… NotesManual ChaptersPG18COMMITUse ROLLBACK to abort a transaction. Issuing COMMIT when not inside a transaction does no harm, but it will provoke a warning message. COMMIT AND CHAIN when not inside a transaction is an er… NotesManual ChaptersPG18COPYCOPY TO can be used with plain tables and populated materialized views. For example, COPY table TO copies the same rows as SELECT * FROM ONLY table. However it doesn't directly support other… NotesManual ChaptersPG18CREATE AGGREGATEIn parameters that specify support function names, you can write a schema name if needed, for example SFUNC = public.sum. Do not write argument types there, however — the argument types of t… NotesManual ChaptersPG18CREATE CASTUse DROP CAST to remove user-defined casts. Remember that if you want to be able to convert types both ways you need to declare casts both ways explicitly. It is normally not necessary to cr… NotesManual ChaptersPG18CREATE COLLATIONCREATE COLLATION takes a SHARE ROW EXCLUSIVE lock, which is self-conflicting, on the pg_collation system catalog, so only one CREATE COLLATION command can run at a time. Use DROP COLLATION t…