Search PostgreSQL documentation

ExamplesManual ChaptersPG18Examples The following example replaces the english dictionary with the swedish dictionary anywhere that english is used within my_config. ALTER TEXT SEARCH CONFIGURATION my_config ALTER MAP…Examples ExamplesManual ChaptersPG18Examples The following example command changes the stopword list for a Snowball-based dictionary. Other parameters remain unchanged. ALTER TEXT SEARCH DICTIONARY my_dict ( StopWords = newrus…Examples ExamplesManual ChaptersPG18Examples To rename a data type: ALTER TYPE electronic_mail RENAME TO email; To change the owner of the type email to joe: ALTER TYPE email OWNER TO joe; To change the schema of the type emai…Examples ExamplesManual ChaptersPG18Examples Change the password for user mapping bob, server foo: ALTER USER MAPPING FOR bob SERVER foo OPTIONS (SET password 'public');Examples ExamplesManual ChaptersPG18Examples To rename the view foo to bar: ALTER VIEW foo RENAME TO bar; To attach a default column value to an updatable view: CREATE TABLE base_table (id int, ts timestamptz); CREATE VIEW a_v…Examples ExamplesManual ChaptersPG18Examples To begin a transaction block: BEGIN;Examples ExamplesManual ChaptersPG18Examples CALL do_db_maintenance();Examples ExamplesManual ChaptersPG18Examples Close the cursor liahona: CLOSE liahona;Examples ExamplesManual ChaptersPG18Examples Cluster the table employees on the basis of its index employees_ind: CLUSTER employees USING employees_ind; Cluster the employees table using the same index that was used before: CL…Examples ExamplesManual ChaptersPG18Examples Attach a comment to the table mytable: COMMENT ON TABLE mytable IS 'This is my table.'; Remove it again: COMMENT ON TABLE mytable IS NULL; Some more examples: COMMENT ON ACCESS METH…Examples ExamplesManual ChaptersPG18Examples Commit the transaction identified by the transaction identifier foobar: COMMIT PREPARED 'foobar';Examples ExamplesManual ChaptersPG18Examples To commit the current transaction and make all changes permanent: COMMIT;Examples ExamplesManual ChaptersPG18Examples The following example copies a table to the client using the vertical bar (|) as the field delimiter: COPY country TO STDOUT (DELIMITER '|'); To copy data from a file into the count…Examples ExamplesManual ChaptersPG18Examples Create an index access method heptree with handler function heptree_handler: CREATE ACCESS METHOD heptree TYPE INDEX HANDLER heptree_handler;Examples ExamplesManual ChaptersPG18Examples See Section 36.12.Examples ExamplesManual ChaptersPG18Examples To create an assignment cast from type bigint to type int4 using the function int4(bigint): CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT; (This cast is alre…Examples ExamplesManual ChaptersPG18Examples To create a collation from the operating system locale fr_FR.utf8 (assuming the current database encoding is UTF8): CREATE COLLATION french (locale = 'fr_FR.utf8'); To create a coll…Examples ExamplesManual ChaptersPG18Examples To create a conversion from encoding UTF8 to LATIN1 using myfunc: CREATE CONVERSION myconv FOR 'UTF8' TO 'LATIN1' FROM myfunc;Examples ExamplesManual ChaptersPG18Examples To create a new database: CREATE DATABASE lusiadas; To create a database sales owned by user salesapp with a default tablespace of salesspace: CREATE DATABASE sales OWNER salesapp T…Examples ExamplesManual ChaptersPG18Examples This example creates the us_postal_code data type and then uses the type in a table definition. A regular expression test is used to verify that the value looks like a valid US post…Examples ExamplesManual ChaptersPG18Examples Forbid the execution of any DDL command: CREATE OR REPLACE FUNCTION abort_any_command() RETURNS event_trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'command % is disabled', t…Examples ExamplesManual ChaptersPG18Examples Install the hstore extension into the current database, placing its objects in schema addons: CREATE EXTENSION hstore SCHEMA addons; Another way to accomplish the same thing: SET se…Examples ExamplesManual ChaptersPG18Examples Create a useless foreign-data wrapper dummy: CREATE FOREIGN DATA WRAPPER dummy; Create a foreign-data wrapper file with handler function file_fdw_handler: CREATE FOREIGN DATA WRAPPE…Examples ExamplesManual ChaptersPG18Examples Create foreign table films, which will be accessed through the server film_server: CREATE FOREIGN TABLE films ( code char(5) NOT NULL, title varchar(40) NOT NULL, did integer NOT NU…Examples ExamplesManual ChaptersPG18Examples Add two integers using an SQL function: CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; The same functi…Examples ExamplesManual ChaptersPG18Examples To create a unique B-tree index on the column title in the table films: CREATE UNIQUE INDEX title_idx ON films (title); To create a unique B-tree index on the column title with incl…Examples ExamplesManual ChaptersPG18Examples A minimal sequence for creating a new procedural language is: CREATE FUNCTION plsample_call_handler() RETURNS language_handler AS '$libdir/plsample' LANGUAGE C; CREATE LANGUAGE plsa…Examples ExamplesManual ChaptersPG18Examples The following example command defines a GiST index operator class for the data type _int4 (array of int4). See the intarray module for the complete example. CREATE OPERATOR CLASS gi…Examples ExamplesManual ChaptersPG18Examples The following command defines a new operator, area-equality, for the data type box: CREATE OPERATOR === ( LEFTARG = box, RIGHTARG = box, FUNCTION = area_equal_function, COMMUTATOR =…Examples ExamplesManual ChaptersPG18Examples CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL AS $$ INSERT INTO tbl VALUES (a); INSERT INTO tbl VALUES (b); $$; or CREATE PROCEDURE insert_data(a integer, b intege…Examples 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