Search PostgreSQL documentation

ExamplesManual ChaptersPG18Examples To make a registration: LISTEN virtual; NOTIFY virtual; Asynchronous notification "virtual" received from server process with PID 8448. Once UNLISTEN has been executed, further NOTI…Examples ExamplesManual ChaptersPG18Examples Change the word Drama to Dramatic in the column kind of the table films: UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'; Adjust temperature entries and reset precipitation …Examples ExamplesManual ChaptersPG18Examples To clean a single table onek, analyze it for the optimizer and print a detailed vacuum activity report: VACUUM (VERBOSE, ANALYZE) onek;Examples ExamplesManual ChaptersPG18Examples A bare VALUES command: VALUES (1, 'one'), (2, 'two'), (3, 'three'); This will return a table of two columns and three rows. It's effectively equivalent to: SELECT 1 AS column1, 'one…Examples ExamplesManual ChaptersPG18F.46.1. Examples Here is an example of selecting a sample of a table with SYSTEM_ROWS. First install the extension: CREATE EXTENSION tsm_system_rows; Then you can use it in a SELECT command,…Examples ExamplesManual ChaptersPG18F.47.1. Examples Here is an example of selecting a sample of a table with SYSTEM_TIME. First install the extension: CREATE EXTENSION tsm_system_time; Then you can use it in a SELECT command,…Examples Examples: Deferred Replication Slot CreationManual ChaptersPG1829.2.3. Examples: Deferred Replication Slot Creation There are some cases (e.g. Section 29.2.1) where, if the remote replication slot was not created automatically, the user must create it m…Examples: Deferred Replication Slot Creation Examples: Set Up Logical ReplicationManual ChaptersPG1829.2.2. Examples: Set Up Logical Replication Create some test tables on the publisher. /* pub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a)); /* pub # */ CREATE TABLE t2(c int, d text, …Examples: Set Up Logical Replication EXCEPTSQL CommandsPG187.4. Combining Queries (UNION, INTERSECT, EXCEPT) # The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is query1 UNION [ALL] …Combining Queries (UNION, INTERSECT, EXCEPT) EXCEPT ClauseManual ChaptersPG18EXCEPT Clause The EXCEPT clause has this general form: select_statement EXCEPT [ ALL | DISTINCT ] select_statement select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO…EXCEPT Clause EXCLUDESQL CommandsPG18EXCLUDE Constraint The EXCLUDE constraint type is a PostgreSQL extension.CREATE TABLE › Compatibility › EXCLUDE Constraint EXCLUDE ConstraintManual ChaptersPG18EXCLUDE Constraint The EXCLUDE constraint type is a PostgreSQL extension.EXCLUDE Constraint Exclusion ConstraintsManual ChaptersPG185.5.6. Exclusion Constraints Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these opera…Exclusion Constraints EXECUTEManual ChaptersPG1841.13.2.2. EXECUTE The PL/pgSQL version of EXECUTE works similarly to the PL/SQL version, but you have to remember to use quote_literal and quote_ident as described in Section 41.5.4. Constr…EXECUTE EXECUTEManual ChaptersPG18EXECUTE EXECUTE — execute a prepared statement Synopsis EXECUTE name [ ( parameter [, ...] ) ]EXECUTE EXECUTESQL CommandsReferenceEXECUTE Cursors & Prepared Statements 10 execute a prepared statement EXECUTE name [ ( parameter [, ...] ) ] EXECUTE is used to execute a previously prepared statement. Since prepared statem…SQL Commands · Cursors & Prepared Statements EXECUTE IMMEDIATEManual ChaptersPG18EXECUTE IMMEDIATE EXECUTE IMMEDIATE — dynamically prepare and execute a statement Synopsis EXECUTE IMMEDIATE stringEXECUTE IMMEDIATE ExecuteGatherWait EventsReferenceExecuteGather IPC 10 Waiting for activity from child process when executing Gather node. 11 12 13 Waiting for activity from a child process while executing a Gather plan node. 14 15 16 17 18…Wait Events · IPC Executing a Command with a Single-Row ResultManual ChaptersPG1841.5.3. Executing a Command with a Single-Row Result The result of an SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable…Executing a Command with a Single-Row Result Executing a Statement with a Result SetManual ChaptersPG1834.5.3. Executing a Statement with a Result Set To execute an SQL statement with a single result row, EXECUTE can be used. To save the result, add an INTO clause. EXEC SQL BEGIN DECLARE SECT…Executing a Statement with a Result Set Executing a Statement with Input ParametersManual ChaptersPG1834.5.2. Executing a Statement with Input Parameters A more powerful way to execute arbitrary SQL statements is to prepare them once and execute the prepared statement as often as you like. I…Executing a Statement with Input Parameters Executing Custom ScansManual ChaptersPG1860.3. Executing Custom Scans When a CustomScan is executed, its execution state is represented by a CustomScanState, which is declared as follows: typedef struct CustomScanState { ScanState …Executing Custom Scans Executing Dynamic CommandsManual ChaptersPG1841.5.4. Executing Dynamic Commands Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different dat…Executing Dynamic Commands Executing SQL CommandsManual ChaptersPG1841.5.2. Executing SQL Commands In general, any SQL command that does not return rows can be executed within a PL/pgSQL function just by writing the command. For example, you could create and…Executing SQL Commands Executing SQL StatementsManual ChaptersPG1834.3.1. Executing SQL Statements Creating a table: EXEC SQL CREATE TABLE foo (number integer, ascii char(16)); EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number); EXEC SQL COMMIT; Inserting ro…Executing SQL Statements Executing Statements without a Result SetManual ChaptersPG1834.5.1. Executing Statements without a Result Set The simplest way to execute an arbitrary SQL statement is to use the command EXECUTE IMMEDIATE. For example: EXEC SQL BEGIN DECLARE SECTION;…Executing Statements without a Result Set ExecutorManual ChaptersPG1851.6. Executor The executor takes the plan created by the planner/optimizer and recursively processes it to extract the required set of rows. This is essentially a demand-pull pipeline mecha…Executor existFunctionsPG18exist ( hstore, text ) → boolean Does hstore contain key? exist('a=>1', 'a') → thstore — hstore key/value datatype › hstore Operators and Functions EXISTSSQL CommandsPG189.24.1. EXISTS # EXISTS (subquery) The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at …Subquery Expressions › EXISTS EXISTSManual ChaptersPG189.24.1. EXISTS EXISTS (subquery) The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at le…EXISTS EXITManual ChaptersPG1841.6.5.2. EXIT EXIT [ label ] [ WHEN boolean-expression ]; If no label is given, the innermost loop is terminated and the statement following END LOOP is executed next. If label is given, it…EXIT Exit StatusManual ChaptersPG18Exit Status pg_isready returns 0 to the shell if the server is accepting connections normally, 1 if the server is rejecting connections (for example during startup), 2 if there was no respon…Exit Status Exit StatusManual ChaptersPG18Exit Status pg_receivewal will exit with status 0 when terminated by the SIGINT or SIGTERM signal. (That is the normal way to end it. Hence it is not an error.) For fatal errors or other sig…Exit Status Exit StatusManual ChaptersPG18Exit Status pg_recvlogical will exit with status 0 when terminated by the SIGINT or SIGTERM signal. (That is the normal way to end it. Hence it is not an error.) For fatal errors or other si…Exit Status Exit StatusManual ChaptersPG18Exit Status psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g., out of memory, file not found), 2 if the connection to the server went bad and th…Exit Status Exit StatusManual ChaptersPG18Exit Status A successful run will exit with status 0. Exit status 1 indicates static problems such as invalid command-line options or internal errors which are supposed to never occur. Early…Exit Status exit_on_errorConfigurationReferenceexit_on_error Error Handling 10 If true, any error will terminate the current session. By default, this is set to false, so that only FATAL errors will terminate the session. 11 12 If on, an…Configuration · Error Handling expFunctionsReferenceexp Mathematical Functions And Operators 10 exponential exp ( dp or numeric ) → (same as input) exponential 11 12 13 Exponential (e raised to the given power) exp ( numeric ) → numeric Expon…Functions · Mathematical Functions And Operators EXPLAINManual ChaptersPG18E.6.3.2.3. EXPLAIN Automatically include BUFFERS output in EXPLAIN ANALYZE (Guillaume Lelarge, David Rowley) § Add full WAL buffer count to EXPLAIN (WAL) output (Bertrand Drouvot) § In EXPLA…EXPLAIN EXPLAINManual ChaptersPG18EXPLAIN EXPLAIN — show the execution plan of a statement Synopsis EXPLAIN [ ( option [, ...] ) ] statement where option can be one of: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean…EXPLAIN