Search PostgreSQL documentation
PG 18 · Browse by category, or enter a name or keyword
Popular entries9775
ExamplesExamples
Destroy the trigger if_dist_exists on the table films:
DROP TRIGGER if_dist_exists ON films;Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
To remove the data type box:
DROP TYPE box;Examples
ExamplesExamples
Drop a user mapping bob, server foo if it exists:
DROP USER MAPPING IF EXISTS FOR bob SERVER foo;Examples
ExamplesExamples
This command will remove the view called kinds:
DROP VIEW kinds;Examples
ExamplesExamples
To commit the current transaction and make all changes permanent:
END;Examples
ExamplesExamples
Examples are given in Examples in the PREPARE documentation.Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
Configure and execute a listen/notify sequence from psql:
LISTEN virtual; NOTIFY virtual; Asynchronous notification "virtual" received from server process with PID 8448.Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
Prepare the current transaction for two-phase commit, using foobar as the transaction identifier:
PREPARE TRANSACTION 'foobar';Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
Set the timezone configuration variable to its default value:
RESET timezone;Examples
ExamplesExamples
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
ExamplesExamples
Roll back the transaction identified by the transaction identifier foobar:
ROLLBACK PREPARED 'foobar';Examples
ExamplesExamples
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
ExamplesExamples
To abort all changes:
ROLLBACK;Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
SELECT SESSION_USER, CURRENT_USER; session_user | current_user --------------+-------------- peter | peter SET ROLE 'paul'; SELECT SESSION_USER, CURRENT_USER; session_user | current…Examples
ExamplesExamples
SELECT SESSION_USER, CURRENT_USER; session_user | current_user --------------+-------------- peter | peter SET SESSION AUTHORIZATION 'paul'; SELECT SESSION_USER, CURRENT_USER; sessi…Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
More results
Find definitions in the PostgreSQL manuals, reference library, and extension catalogue.
pg17: choose a version ex: extensions only ↑↓ select ↵ open