Search PostgreSQL documentation

ExamplesManual ChaptersPG18Examples To drop the routine foo for type integer: DROP ROUTINE foo(integer); This command will work independent of whether foo is an aggregate, function, or procedure.Examples ExamplesManual ChaptersPG18Examples To drop the rewrite rule newrule: DROP RULE newrule ON mytable;Examples ExamplesManual ChaptersPG18Examples To remove schema mystuff from the database, along with everything it contains: DROP SCHEMA mystuff CASCADE;Examples ExamplesManual ChaptersPG18Examples To remove the sequence serial: DROP SEQUENCE serial;Examples ExamplesManual ChaptersPG18Examples Drop a server foo if it exists: DROP SERVER IF EXISTS foo;Examples ExamplesManual ChaptersPG18Examples To destroy two statistics objects in different schemas, without failing if they don't exist: DROP STATISTICS IF EXISTS accounting.users_uid_creation, public.grants_user_role;Examples ExamplesManual ChaptersPG18Examples Drop a subscription: DROP SUBSCRIPTION mysub;Examples ExamplesManual ChaptersPG18Examples To destroy two tables, films and distributors: DROP TABLE films, distributors;Examples ExamplesManual ChaptersPG18Examples To remove tablespace mystuff from the system: DROP TABLESPACE mystuff;Examples ExamplesManual ChaptersPG18Examples To drop the transform for type hstore and language plpython3u: DROP TRANSFORM FOR hstore LANGUAGE plpython3u;Examples ExamplesManual ChaptersPG18Examples Destroy the trigger if_dist_exists on the table films: DROP TRIGGER if_dist_exists ON films;Examples ExamplesManual ChaptersPG18Examples Remove the text search configuration my_english: DROP TEXT SEARCH CONFIGURATION my_english; This command will not succeed if there are any existing indexes that reference the config…Examples ExamplesManual ChaptersPG18Examples Remove the text search dictionary english: DROP TEXT SEARCH DICTIONARY english; This command will not succeed if there are any existing text search configurations that use the dicti…Examples ExamplesManual ChaptersPG18Examples Remove the text search parser my_parser: DROP TEXT SEARCH PARSER my_parser; This command will not succeed if there are any existing text search configurations that use the parser. A…Examples ExamplesManual ChaptersPG18Examples Remove the text search template thesaurus: DROP TEXT SEARCH TEMPLATE thesaurus; This command will not succeed if there are any existing text search dictionaries that use the templat…Examples ExamplesManual ChaptersPG18Examples To remove the data type box: DROP TYPE box;Examples ExamplesManual ChaptersPG18Examples Drop a user mapping bob, server foo if it exists: DROP USER MAPPING IF EXISTS FOR bob SERVER foo;Examples ExamplesManual ChaptersPG18Examples This command will remove the view called kinds: DROP VIEW kinds;Examples ExamplesManual ChaptersPG18Examples To commit the current transaction and make all changes permanent: END;Examples ExamplesManual ChaptersPG18Examples Examples are given in Examples in the PREPARE documentation.Examples ExamplesManual ChaptersPG18Examples To show the plan for a simple query on a table with a single integer column and 10000 rows: EXPLAIN SELECT * FROM foo; QUERY PLAN ---------------------------------------------------…Examples ExamplesManual ChaptersPG18Examples The following example traverses a table using a cursor: BEGIN WORK; -- Set up a cursor: DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films; -- Fetch the first 5 rows in the curso…Examples ExamplesManual ChaptersPG18Examples Grant insert privilege to all users on table films: GRANT INSERT ON films TO PUBLIC; Grant all available privileges to user manuel on view kinds: GRANT ALL PRIVILEGES ON kinds TO ma…Examples ExamplesManual ChaptersPG18Examples Import table definitions from a remote schema foreign_films on server film_server, creating the foreign tables in local schema films: IMPORT FOREIGN SCHEMA foreign_films FROM SERVER…Examples ExamplesManual ChaptersPG18Examples Insert a single row into table films: INSERT INTO films VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'); In this example, the len column is omitted and theref…Examples ExamplesManual ChaptersPG18Examples Configure and execute a listen/notify sequence from psql: LISTEN virtual; NOTIFY virtual; Asynchronous notification "virtual" received from server process with PID 8448.Examples ExamplesManual ChaptersPG18Examples Obtain a SHARE lock on a primary key table when going to perform inserts into a foreign key table: BEGIN WORK; LOCK TABLE films IN SHARE MODE; SELECT id FROM films WHERE name = 'Sta…Examples ExamplesManual ChaptersPG18Examples Perform maintenance on customer_accounts based upon new recent_transactions. MERGE INTO customer_account ca USING recent_transactions t ON t.customer_id = ca.customer_id WHEN MATCHE…Examples ExamplesManual ChaptersPG18Examples BEGIN WORK; DECLARE liahona CURSOR FOR SELECT * FROM films; -- Skip the first 5 rows: MOVE FORWARD 5 IN liahona; MOVE 5 -- Fetch the 6th row from the cursor liahona: FETCH 1 FROM li…Examples ExamplesManual ChaptersPG18Examples Configure and execute a listen/notify sequence from psql: LISTEN virtual; NOTIFY virtual; Asynchronous notification "virtual" received from server process with PID 8448. NOTIFY virt…Examples ExamplesManual ChaptersPG18Examples Prepare the current transaction for two-phase commit, using foobar as the transaction identifier: PREPARE TRANSACTION 'foobar';Examples ExamplesManual ChaptersPG18Examples Create a prepared statement for an INSERT statement, and then execute it: PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO foo VALUES($1, $2, $3, $4); EXECUTE fooplan(1, 'H…Examples ExamplesManual ChaptersPG18Examples This command will replace the contents of the materialized view called order_summary using the query from the materialized view's definition, and leave it in a scannable state: REFR…Examples ExamplesManual ChaptersPG18Examples Rebuild a single index: REINDEX INDEX my_index; Rebuild all the indexes on the table my_table: REINDEX TABLE my_table; Rebuild all indexes in a particular database, without trusting…Examples ExamplesManual ChaptersPG18Examples To establish and later release a savepoint: BEGIN; INSERT INTO table1 VALUES (3); SAVEPOINT my_savepoint; INSERT INTO table1 VALUES (4); RELEASE SAVEPOINT my_savepoint; COMMIT; The …Examples ExamplesManual ChaptersPG18Examples Set the timezone configuration variable to its default value: RESET timezone;Examples ExamplesManual ChaptersPG18Examples Revoke insert privilege for the public on table films: REVOKE INSERT ON films FROM PUBLIC; Revoke all privileges from user manuel on view kinds: REVOKE ALL PRIVILEGES ON kinds FROM …Examples ExamplesManual ChaptersPG18Examples Roll back the transaction identified by the transaction identifier foobar: ROLLBACK PREPARED 'foobar';Examples ExamplesManual ChaptersPG18Examples To undo the effects of the commands executed after my_savepoint was established: ROLLBACK TO SAVEPOINT my_savepoint; Cursor positions are not affected by savepoint rollback: BEGIN; …Examples ExamplesManual ChaptersPG18Examples To abort all changes: ROLLBACK;Examples