Search PostgreSQL documentation
PG 18 · Browse by category, or enter a name or keyword
Category index4025
ExamplesExamples
int main(void) { EXEC SQL CONNECT TO testdb AS con1 USER testuser; EXEC SQL CONNECT TO testdb AS con2 USER testuser; EXEC SQL CONNECT TO testdb AS con3 USER testuser; EXEC SQL DISCO…Examples
ExamplesExamples
Here is an example that executes an INSERT statement using EXECUTE IMMEDIATE and a host variable named command:
sprintf(command, "INSERT INTO test (name, amount, letter) VALUES ('db…Examples
ExamplesExamples
An example to retrieve the number of columns in a result set:
EXEC SQL GET DESCRIPTOR d :d_count = COUNT;
An example to retrieve a data length in the first column:
EXEC SQL GET DESC…Examples
ExamplesExamples
EXEC SQL OPEN a; EXEC SQL OPEN d USING 1, 'test'; EXEC SQL OPEN c1 USING SQL DESCRIPTOR mydesc; EXEC SQL OPEN :curname1;Examples
ExamplesExamples
char *stmt = "SELECT * FROM test1 WHERE a = ? AND b = ?"; EXEC SQL ALLOCATE DESCRIPTOR outdesc; EXEC SQL PREPARE foo FROM :stmt; EXEC SQL EXECUTE foo USING SQL DESCRIPTOR indesc INT…Examples
ExamplesExamples
EXEC SQL SET CONNECTION TO con2; EXEC SQL SET CONNECTION = con1;Examples
ExamplesExamples
EXEC SQL SET DESCRIPTOR indesc COUNT = 1; EXEC SQL SET DESCRIPTOR indesc VALUE 1 DATA = 2; EXEC SQL SET DESCRIPTOR indesc VALUE 1 DATA = :val1; EXEC SQL SET DESCRIPTOR indesc VALUE …Examples
ExamplesExamples
EXEC SQL TYPE customer IS struct { varchar name[50]; int phone; }; EXEC SQL TYPE cust_ind IS struct ind { short name_ind; short phone_ind; }; EXEC SQL TYPE c IS char reference; EXEC…Examples
ExamplesExamples
Exec sql begin declare section; short a; exec sql end declare section; EXEC SQL VAR a IS int;Examples
ExamplesExamples
EXEC SQL WHENEVER NOT FOUND CONTINUE; EXEC SQL WHENEVER NOT FOUND DO BREAK; EXEC SQL WHENEVER NOT FOUND DO CONTINUE; EXEC SQL WHENEVER SQLWARNING SQLPRINT; EXEC SQL WHENEVER SQLWARN…Examples
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
More results
Find definitions in the PostgreSQL manuals, reference library, and extension catalogue.
pg17: choose a version ex: extensions only ↑↓ select ↵ open