Search PostgreSQL documentation
PG 18 · Browse by category, or enter a name or keyword
Popular entries9775
Examples65.4.7. Examples
The core PostgreSQL distribution includes the GIN operator classes previously shown in Table 65.3. The following contrib modules also contain GIN operator classes:
btree_gin…Examples
Examples65.2.5. Examples
The PostgreSQL source distribution includes several examples of index methods implemented using GiST. The core system currently provides text search support (indexing for ts…Examples
ExamplesF.17.4. Examples
Add a key, or update an existing key with a new value:
UPDATE tab SET h['c'] = '3';
Another way to do the same thing is:
UPDATE tab SET h = h || hstore('c', '3');
If multipl…Examples
ExamplesF.20.5. Examples
--Using the types directly: SELECT isbn('978-0-393-04002-9'); SELECT isbn13('0901690546'); SELECT issn('1436-4522'); --Casting types: -- note that you can only cast from ean…Examples
Examples29.5.1. Examples
Create a table t1 to be used in the following example.
/* pub # */ CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
Create a publication p1.…Examples
Examples29.4.7. Examples
Create some tables to be used in the following examples.
/* pub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); /* pub # */ CREATE TABLE t2(d int, e int, f int…Examples
ExamplesExamples
$ # what's in this database server, anyway? $ oid2name All databases: Oid Database Name Tablespace ---------------------------------- 17228 alvherre pg_default 17255 regression pg_d…Examples
ExamplesExamples
On Linux or Unix systems, you might use:
archive_cleanup_command = 'pg_archivecleanup -d /mnt/standby/archive %r 2>>cleanup.log'
where the archive directory is physically located on…Examples
ExamplesF.38.10. Examples
Here is an example of creating a foreign table with postgres_fdw. First install the extension:
CREATE EXTENSION postgres_fdw;
Then create a foreign server using CREATE SERV…Examples
Examples8.17.2. Examples
CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)'); -- Containment SELECT int4range(10, 20) @…Examples
Examples65.3.5. Examples
The PostgreSQL source distribution includes several examples of index operator classes for SP-GiST, as described in Table 65.2. Look into src/backend/access/spgist/ and src/…Examples
Examples45.6. Examples
This section contains a very simple example of SPI usage. The C function execq takes an SQL command as its first argument and a row count as its second, executes the command u…Examples
ExamplesExamples
To abort all changes:
ABORT;Examples
ExamplesExamples
To rename the aggregate function myavg for type integer to my_average:
ALTER AGGREGATE myavg(integer) RENAME TO my_average;
To change the owner of the aggregate function myavg for t…Examples
ExamplesExamples
To rename the collation de_DE to german:
ALTER COLLATION "de_DE" RENAME TO german;
To change the owner of the collation en_US to joe:
ALTER COLLATION "en_US" OWNER TO joe;Examples
ExamplesExamples
To rename the conversion iso_8859_1_to_utf8 to latin1_to_unicode:
ALTER CONVERSION iso_8859_1_to_utf8 RENAME TO latin1_to_unicode;
To change the owner of the conversion iso_8859_1_t…Examples
ExamplesExamples
To disable index scans by default in the database test:
ALTER DATABASE test SET enable_indexscan TO off;Examples
ExamplesExamples
Grant SELECT privilege to everyone for all tables (and views) you subsequently create in schema myschema, and allow role webuser to INSERT into them too:
ALTER DEFAULT PRIVILEGES IN…Examples
ExamplesExamples
To add a NOT NULL constraint to a domain:
ALTER DOMAIN zipcode SET NOT NULL;
To remove a NOT NULL constraint from a domain:
ALTER DOMAIN zipcode DROP NOT NULL;
To add a check constr…Examples
ExamplesExamples
To update the hstore extension to version 2.0:
ALTER EXTENSION hstore UPDATE TO '2.0';
To change the schema of the hstore extension to utils:
ALTER EXTENSION hstore SET SCHEMA utils…Examples
ExamplesExamples
Change a foreign-data wrapper dbi, add option foo, drop bar:
ALTER FOREIGN DATA WRAPPER dbi OPTIONS (ADD foo '1', DROP bar);
Change the foreign-data wrapper dbi validator to bob.myv…Examples
ExamplesExamples
To mark a column as not-null:
ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
To change options of a foreign table:
ALTER FOREIGN TABLE myschema.distributors OPTI…Examples
ExamplesExamples
To rename the function sqrt for type integer to square_root:
ALTER FUNCTION sqrt(integer) RENAME TO square_root;
To change the owner of the function sqrt for type integer to joe:
AL…Examples
ExamplesExamples
Add users to a group:
ALTER GROUP staff ADD USER karl, john;
Remove a user from a group:
ALTER GROUP workers DROP USER beth;Examples
ExamplesExamples
To rename an existing index:
ALTER INDEX distributors RENAME TO suppliers;
To move an index to a different tablespace:
ALTER INDEX distributors SET TABLESPACE fasttablespace;
To cha…Examples
ExamplesExamples
To rename the materialized view foo to bar:
ALTER MATERIALIZED VIEW foo RENAME TO bar;Examples
ExamplesExamples
Change the owner of a custom operator a @@ b for type text:
ALTER OPERATOR @@ (text, text) OWNER TO joe;
Change the restriction and join selectivity estimator functions of a custom …Examples
ExamplesExamples
The following example command adds cross-data-type operators and support functions to an operator family that already contains B-tree operator classes for data types int4 and int2.
…Examples
ExamplesExamples
To rename the procedure insert_data with two arguments of type integer to insert_record:
ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
To change the owner o…Examples
ExamplesExamples
Change the publication to publish only deletes and updates:
ALTER PUBLICATION noinsert SET (publish = 'update, delete');
Add some tables to the publication:
ALTER PUBLICATION mypubl…Examples
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
More results
Find definitions in the PostgreSQL manuals, reference library, and extension catalogue.
pg17: choose a version ex: extensions only ↑↓ select ↵ open