Search PostgreSQL documentation
PG 18 · Browse by category, or enter a name or keyword
Popular entries9775
ExamplesExamples
To create a transform for type hstore and language plpython3u, first set up the type and the language:
CREATE TYPE hstore ...; CREATE EXTENSION plpython3u;
Then create the necessary…Examples
ExamplesExamples
Execute the function check_account_update whenever a row of the table accounts is about to be updated:
CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE FUN…Examples
ExamplesExamples
The following example command creates a Snowball-based dictionary with a nonstandard list of stop words.
CREATE TEXT SEARCH DICTIONARY my_russian ( template = snowball, language = r…Examples
ExamplesExamples
This example creates a composite type and uses it in a function definition:
CREATE TYPE compfoo AS (f1 int, f2 text); CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$ SELECT foo…Examples
ExamplesExamples
Create a user mapping for user bob, server foo:
CREATE USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'secret');Examples
ExamplesExamples
Create a view consisting of all comedy films:
CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy';
This will create a view containing the columns that are in the film …Examples
ExamplesExamples
To declare a cursor:
DECLARE liahona CURSOR FOR SELECT * FROM films;
See FETCH for more examples of cursor usage.Examples
ExamplesExamples
Delete all films but musicals:
DELETE FROM films WHERE kind <> 'Musical';
Clear the table films:
DELETE FROM films;
Delete completed tasks, returning full details of the deleted row…Examples
ExamplesExamples
Grant all privileges on all views in schema public to role webuser:
DO $$DECLARE r record; BEGIN FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_…Examples
ExamplesExamples
Drop the access method heptree:
DROP ACCESS METHOD heptree;Examples
ExamplesExamples
To remove the aggregate function myavg for type integer:
DROP AGGREGATE myavg(integer);
To remove the hypothetical-set aggregate function myrank, which takes an arbitrary list of or…Examples
ExamplesExamples
To drop the cast from type text to type int:
DROP CAST (text AS int);Examples
ExamplesExamples
To drop the collation named german:
DROP COLLATION german;Examples
ExamplesExamples
To drop the conversion named myname:
DROP CONVERSION myname;Examples
ExamplesExamples
To remove the domain box:
DROP DOMAIN box;Examples
ExamplesExamples
Destroy the trigger snitch:
DROP EVENT TRIGGER snitch;Examples
ExamplesExamples
To remove the extension hstore from the current database:
DROP EXTENSION hstore;
This command will fail if any of hstore's objects are in use in the database, for example if any tab…Examples
ExamplesExamples
Drop the foreign-data wrapper dbi:
DROP FOREIGN DATA WRAPPER dbi;Examples
ExamplesExamples
To destroy two foreign tables, films and distributors:
DROP FOREIGN TABLE films, distributors;Examples
ExamplesExamples
This command removes the square root function:
DROP FUNCTION sqrt(integer);
Drop multiple functions in one command:
DROP FUNCTION sqrt(integer), sqrt(bigint);
If the function name i…Examples
ExamplesExamples
This command will remove the index title_idx:
DROP INDEX title_idx;Examples
ExamplesExamples
This command removes the procedural language plsample:
DROP LANGUAGE plsample;Examples
ExamplesExamples
This command will remove the materialized view called order_summary:
DROP MATERIALIZED VIEW order_summary;Examples
ExamplesExamples
Remove the B-tree operator class widget_ops:
DROP OPERATOR CLASS widget_ops USING btree;
This command will not succeed if there are any existing indexes that use the operator class.…Examples
ExamplesExamples
Remove the power operator a^b for type integer:
DROP OPERATOR ^ (integer, integer);
Remove the bitwise-complement prefix operator ~b for type bit:
DROP OPERATOR ~ (none, bit);
Remov…Examples
ExamplesExamples
Remove the B-tree operator family float_ops:
DROP OPERATOR FAMILY float_ops USING btree;
This command will not succeed if there are any existing indexes that use operator classes wi…Examples
ExamplesExamples
To drop the policy called p1 on the table named my_table:
DROP POLICY p1 ON my_table;Examples
ExamplesExamples
If there is only one procedure do_db_maintenance, this command is sufficient to drop it:
DROP PROCEDURE do_db_maintenance;
Given this procedure definition:
CREATE PROCEDURE do_db_ma…Examples
ExamplesExamples
Drop a publication:
DROP PUBLICATION mypublication;Examples
ExamplesExamples
To drop a role:
DROP ROLE jonathan;Examples
ExamplesExamples
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
ExamplesExamples
To drop the rewrite rule newrule:
DROP RULE newrule ON mytable;Examples
ExamplesExamples
To remove schema mystuff from the database, along with everything it contains:
DROP SCHEMA mystuff CASCADE;Examples
ExamplesExamples
To remove the sequence serial:
DROP SEQUENCE serial;Examples
ExamplesExamples
Drop a server foo if it exists:
DROP SERVER IF EXISTS foo;Examples
ExamplesExamples
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
ExamplesExamples
Drop a subscription:
DROP SUBSCRIPTION mysub;Examples
ExamplesExamples
To destroy two tables, films and distributors:
DROP TABLE films, distributors;Examples
ExamplesExamples
To remove tablespace mystuff from the system:
DROP TABLESPACE mystuff;Examples
ExamplesExamples
To drop the transform for type hstore and language plpython3u:
DROP TRANSFORM FOR hstore LANGUAGE plpython3u;Examples
More results
Find definitions in the PostgreSQL manuals, reference library, and extension catalogue.
pg17: choose a version ex: extensions only ↑↓ select ↵ open