Search PostgreSQL documentation

ExamplesManual ChaptersPG18Examples To establish a savepoint and later undo the effects of all commands executed after it was established: BEGIN; INSERT INTO table1 VALUES (1); SAVEPOINT my_savepoint; INSERT INTO tabl…Examples ExamplesManual ChaptersPG18Examples The following example shows how the security label of a table could be set or changed: SECURITY LABEL FOR selinux ON TABLE mytable IS 'system_u:object_r:sepgsql_table_t:s0'; To remo…Examples ExamplesManual ChaptersPG18Examples To join the table films with the table distributors: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d JOIN films f USING (did); title | did | name | date_prod …Examples ExamplesManual ChaptersPG18Examples Create a new table films_recent consisting of only recent entries from the table films: SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';Examples ExamplesManual ChaptersPG18Examples SELECT SESSION_USER, CURRENT_USER; session_user | current_user --------------+-------------- peter | peter SET ROLE 'paul'; SELECT SESSION_USER, CURRENT_USER; session_user | current…Examples ExamplesManual ChaptersPG18Examples SELECT SESSION_USER, CURRENT_USER; session_user | current_user --------------+-------------- peter | peter SET SESSION AUTHORIZATION 'paul'; SELECT SESSION_USER, CURRENT_USER; sessi…Examples ExamplesManual ChaptersPG18Examples To begin a new transaction with the same snapshot as an already existing transaction, first export the snapshot from the existing transaction. That will return the snapshot identifi…Examples ExamplesManual ChaptersPG18Examples Set the schema search path: SET search_path TO my_schema, public; Set the style of date to traditional POSTGRES with “day before month” input convention: SET datestyle TO postgres, …Examples ExamplesManual ChaptersPG18Examples Show the current setting of the parameter DateStyle: SHOW DateStyle; DateStyle ----------- ISO, MDY (1 row) Show the current setting of the parameter geqo: SHOW geqo; geqo ------ on…Examples ExamplesManual ChaptersPG18Examples Truncate the tables bigtable and fattable: TRUNCATE bigtable, fattable; The same, and also reset any associated sequence generators: TRUNCATE bigtable, fattable RESTART IDENTITY; Tr…Examples 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 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 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 EXECUTE IMMEDIATEManual ChaptersPG18EXECUTE IMMEDIATE EXECUTE IMMEDIATE — dynamically prepare and execute a statement Synopsis EXECUTE IMMEDIATE stringEXECUTE IMMEDIATE 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 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