Search PostgreSQL documentation

ExamplesManual ChaptersPG18Examples Create a publication that publishes all changes in two tables: CREATE PUBLICATION mypublication FOR TABLE users, departments; Create a publication that publishes all changes from ac…Examples ExamplesManual ChaptersPG18Examples Create a role that can log in, but don't give it a password: CREATE ROLE jonathan LOGIN; Create a role with a password: CREATE USER davide WITH PASSWORD 'jw8s0F4'; (CREATE USER is t…Examples ExamplesManual ChaptersPG18Examples Create a schema: CREATE SCHEMA myschema; Create a schema for user joe; the schema will also be named joe: CREATE SCHEMA AUTHORIZATION joe; Create a schema named test that will be ow…Examples ExamplesManual ChaptersPG18Examples Create an ascending sequence called serial, starting at 101: CREATE SEQUENCE serial START 101; Select the next number from this sequence: SELECT nextval('serial'); nextval ---------…Examples ExamplesManual ChaptersPG18Examples Create a server myserver that uses the foreign-data wrapper postgres_fdw: CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname 'foodb', port '5432');…Examples ExamplesManual ChaptersPG18Examples Create table t1 with two functionally dependent columns, i.e., knowledge of a value in the first column is sufficient for determining the value in the other column. Then functional …Examples ExamplesManual ChaptersPG18Examples Create a subscription to a remote server that replicates tables in the publications mypublication and insert_only and starts replicating immediately on commit: CREATE SUBSCRIPTION m…Examples ExamplesManual ChaptersPG18Examples Create table films and table distributors: CREATE TABLE films ( code char(5) CONSTRAINT firstkey PRIMARY KEY, title varchar(40) NOT NULL, did integer NOT NULL, date_prod date, kind …Examples ExamplesManual ChaptersPG18Examples Create a new table films_recent consisting of only recent entries from the table films: CREATE TABLE films_recent AS SELECT * FROM films WHERE date_prod >= '2002-01-01'; To copy a t…Examples ExamplesManual ChaptersPG18Examples To create a tablespace dbspace at file system location /data/dbs, first create the directory using operating system facilities and set the correct ownership: mkdir /data/dbs chown p…Examples ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples Create a user mapping for user bob, server foo: CREATE USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'secret');Examples ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples To declare a cursor: DECLARE liahona CURSOR FOR SELECT * FROM films; See FETCH for more examples of cursor usage.Examples ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples Drop the access method heptree: DROP ACCESS METHOD heptree;Examples ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples To drop the cast from type text to type int: DROP CAST (text AS int);Examples ExamplesManual ChaptersPG18Examples To drop the collation named german: DROP COLLATION german;Examples ExamplesManual ChaptersPG18Examples To drop the conversion named myname: DROP CONVERSION myname;Examples ExamplesManual ChaptersPG18Examples To remove the domain box: DROP DOMAIN box;Examples ExamplesManual ChaptersPG18Examples Destroy the trigger snitch: DROP EVENT TRIGGER snitch;Examples ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples Drop the foreign-data wrapper dbi: DROP FOREIGN DATA WRAPPER dbi;Examples ExamplesManual ChaptersPG18Examples To destroy two foreign tables, films and distributors: DROP FOREIGN TABLE films, distributors;Examples ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples This command will remove the index title_idx: DROP INDEX title_idx;Examples ExamplesManual ChaptersPG18Examples This command removes the procedural language plsample: DROP LANGUAGE plsample;Examples ExamplesManual ChaptersPG18Examples This command will remove the materialized view called order_summary: DROP MATERIALIZED VIEW order_summary;Examples ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples To drop the policy called p1 on the table named my_table: DROP POLICY p1 ON my_table;Examples ExamplesManual ChaptersPG18Examples 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 ExamplesManual ChaptersPG18Examples Drop a publication: DROP PUBLICATION mypublication;Examples ExamplesManual ChaptersPG18Examples To drop a role: DROP ROLE jonathan;Examples