Search PostgreSQL documentation
PG 18 · Browse by category, or enter a name or keyword
Category index4025
ExamplesExamples
Change a role's password:
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
Remove a role's password:
ALTER ROLE davide WITH PASSWORD NULL;
Change a password expiration date, specifying th…Examples
ExamplesExamples
To rename the routine foo for type integer to foobar:
ALTER ROUTINE foo(integer) RENAME TO foobar;
This command will work independent of whether foo is an aggregate, function, or pr…Examples
ExamplesExamples
To rename an existing rule:
ALTER RULE notify_all ON emp RENAME TO notify_me;Examples
ExamplesExamples
Restart a sequence called serial, at 105:
ALTER SEQUENCE serial RESTART WITH 105;Examples
ExamplesExamples
Alter server foo, add connection options:
ALTER SERVER foo OPTIONS (host 'foo', dbname 'foodb');
Alter server foo, change version, change host option:
ALTER SERVER foo VERSION '8.4'…Examples
ExamplesExamples
Change the publication subscribed by a subscription to insert_only:
ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;
Disable (stop) the subscription:
ALTER SUBSCRIPTION mysub D…Examples
ExamplesExamples
Set the wal_level:
ALTER SYSTEM SET wal_level = replica;
Undo that, restoring whatever setting was effective in postgresql.conf:
ALTER SYSTEM RESET wal_level;Examples
ExamplesExamples
To add a column of type varchar to a table:
ALTER TABLE distributors ADD COLUMN address varchar(30);
That will cause all existing rows in the table to be filled with null values for…Examples
ExamplesExamples
Rename tablespace index_space to fast_raid:
ALTER TABLESPACE index_space RENAME TO fast_raid;
Change the owner of tablespace index_space:
ALTER TABLESPACE index_space OWNER TO mary;Examples
ExamplesExamples
To rename an existing trigger:
ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;
To mark a trigger as being dependent on an extension:
ALTER TRIGGER emp_stamp ON emp DEPENDS …Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
Change the password for user mapping bob, server foo:
ALTER USER MAPPING FOR bob SERVER foo OPTIONS (SET password 'public');Examples
ExamplesExamples
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
ExamplesExamples
To begin a transaction block:
BEGIN;Examples
ExamplesExamples
CALL do_db_maintenance();Examples
ExamplesExamples
Close the cursor liahona:
CLOSE liahona;Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
Commit the transaction identified by the transaction identifier foobar:
COMMIT PREPARED 'foobar';Examples
ExamplesExamples
To commit the current transaction and make all changes permanent:
COMMIT;Examples
ExamplesExamples
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
ExamplesExamples
Create an index access method heptree with handler function heptree_handler:
CREATE ACCESS METHOD heptree TYPE INDEX HANDLER heptree_handler;Examples
ExamplesExamples
See Section 36.12.Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
To create a conversion from encoding UTF8 to LATIN1 using myfunc:
CREATE CONVERSION myconv FOR 'UTF8' TO 'LATIN1' FROM myfunc;Examples
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
ExamplesExamples
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
More results
Find definitions in the PostgreSQL manuals, reference library, and extension catalogue.
pg17: choose a version ex: extensions only ↑↓ select ↵ open