Search PostgreSQL documentation
PG 18 · Browse by category, or enter a name or keyword
Category index4025
ExamplesExamples
If you have an embedded SQL C source file named prog1.pgc, you can create an executable program using the following sequence of commands:
ecpg prog1.pgc cc -I/usr/local/pgsql/includ…Examples
ExamplesExamplesExamples
ExamplesExamples
To dump all databases:
$ pg_dumpall > db.out
To restore database(s) from this file, you can use:
$ psql -X -f db.out -d postgres
It is not important which database you connect to he…Examples
ExamplesExamples
Standard Usage:
$ pg_isready /tmp:5432 - accepting connections $ echo $? 0
Running with connection parameters to a PostgreSQL cluster in startup:
$ pg_isready -h localhost -p 5433 l…Examples
ExamplesExamples
To create a base backup of the server at mydbserver and store it in the local directory /usr/local/pgsql/data:
$ pg_basebackup -h mydbserver -D /usr/local/pgsql/data
To create a bac…Examples
ExamplesExamples
To create a logical replica for databases hr and finance from a physical replica at foo:
$ pg_createsubscriber -D /usr/local/pgsql/data -P "host=foo" -d hr -d financeExamples
ExamplesExamples
To dump a database called mydb into an SQL-script file:
$ pg_dump mydb > db.sql
To reload such a script into a (freshly created) database named newdb:
$ psql -X -d newdb -f db.sql
T…Examples
ExamplesExamples
To stream the write-ahead log from the server at mydbserver and store it in the local directory /usr/local/pgsql/archive:
$ pg_receivewal -h mydbserver -D /usr/local/pgsql/archiveExamples
ExamplesExamples
See Section 47.1 for an example.Examples
ExamplesExamples
Assume we have dumped a database called mydb into a custom-format dump file:
$ pg_dump -Fc mydb > db.dump
To drop the database and recreate it from the dump:
$ dropdb mydb $ pg_rest…Examples
ExamplesExamples
To create a base backup of the server at mydbserver and verify the integrity of the backup:
$ pg_basebackup -h mydbserver -D /usr/local/pgsql/data $ pg_verifybackup /usr/local/pgsql…Examples
ExamplesExamples
To start postgres in the background using default values, type:
$ nohup postgres >logfile 2>&1 </dev/null &
To start postgres with a specific port, e.g., 1234:
$ postgres -p 1234
To…Examples
ExamplesExamples
The first example shows how to spread a command over several lines of input. Notice the changing prompt:
testdb=> CREATE TABLE my_table ( testdb(> first integer not null default 0, …Examples
ExamplesExamples
To reindex the database test:
$ reindexdb test
To reindex the table foo and the index bar in a database named abcd:
$ reindexdb --table=foo --index=bar abcdExamples
ExamplesExamples
To clean the database test:
$ vacuumdb test
To clean and analyze for the optimizer a database named bigdb:
$ vacuumdb --analyze bigdb
To clean a single table foo in a database named…Examples
ExamplesF.6.2. Examples
This is an example of creating a bloom index:
CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3) WITH (length=80, col1=2, col2=2, col3=4);
The index is created with a sig…Examples
ExamplesExamples
SELECT dblink_build_sql_delete('"MyFoo"', '1 2', 2, '{"1", "b"}'); dblink_build_sql_delete --------------------------------------------- DELETE FROM "MyFoo" WHERE f1='1' AND f2='b' …Examples
ExamplesExamples
SELECT dblink_build_sql_insert('foo', '1 2', 2, '{"1", "a"}', '{"1", "b''a"}'); dblink_build_sql_insert -------------------------------------------------- INSERT INTO foo(f1,f2,f3) …Examples
ExamplesExamples
SELECT dblink_build_sql_update('foo', '1 2', 2, '{"1", "a"}', '{"1", "b"}'); dblink_build_sql_update ------------------------------------------------------------- UPDATE foo SET f1=…Examples
ExamplesExamples
SELECT dblink_cancel_query('dtest1');Examples
ExamplesExamples
SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT dblink_open('foo', 'select proname, prosrc from pg_proc'); dblink_…Examples
ExamplesExamples
SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=')…Examples
ExamplesExamples
SELECT dblink_disconnect(); dblink_disconnect ------------------- OK (1 row) SELECT dblink_disconnect('myconn'); dblink_disconnect ------------------- OK (1 row)Examples
ExamplesExamples
SELECT dblink_error_message('dtest1');Examples
ExamplesExamples
SELECT dblink_connect('dbname=dblink_test_standby'); dblink_connect ---------------- OK (1 row) SELECT dblink_exec('insert into foo values(21, ''z'', ''{"a0","b0","c0"}'');'); dblin…Examples
ExamplesExamples
SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT dblink_open('foo', 'select proname, prosrc from pg_proc where pron…Examples
ExamplesExamples
SELECT * FROM dblink('dbname=postgres options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc …Examples
ExamplesExamples
SELECT dblink_get_connections();Examples
ExamplesExamples
SELECT dblink_exec('LISTEN virtual'); dblink_exec ------------- LISTEN (1 row) SELECT * FROM dblink_get_notify(); notify_name | be_pid | extra -------------+--------+------- (0 rows…Examples
ExamplesExamples
CREATE TABLE foobar ( f1 int, f2 int, f3 int, PRIMARY KEY (f1, f2, f3) ); CREATE TABLE SELECT * FROM dblink_get_pkey('foobar'); position | colname ----------+--------- 1 | f1 2 | f2…Examples
ExamplesExamples
contrib_regression=# SELECT dblink_connect('dtest1', 'dbname=contrib_regression'); dblink_connect ---------------- OK (1 row) contrib_regression=# SELECT * FROM contrib_regression-#…Examples
ExamplesExamples
SELECT dblink_is_busy('dtest1');Examples
ExamplesExamples
SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT dblink_open('foo', 'select proname, prosrc from pg_proc'); dblink_…Examples
ExamplesExamples
SELECT dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3');Examples
ExamplesExamples
EXEC SQL ALLOCATE DESCRIPTOR mydesc;Examples
ExamplesExamples
Here a several variants for specifying connection parameters:
EXEC SQL CONNECT TO "connectdb" AS main; EXEC SQL CONNECT TO "connectdb" AS second; EXEC SQL CONNECT TO "unix:postgresq…Examples
ExamplesExamples
EXEC SQL DEALLOCATE DESCRIPTOR mydesc;Examples
ExamplesExamples
EXEC SQL CONNECT TO postgres AS con1; EXEC SQL AT con1 DECLARE sql_stmt STATEMENT; EXEC SQL DECLARE cursor_name CURSOR FOR sql_stmt; EXEC SQL PREPARE sql_stmt FROM :dyn_string; EXEC…Examples
ExamplesExamples
Examples declaring a cursor for a query:
EXEC SQL DECLARE C CURSOR FOR SELECT * FROM My_Table; EXEC SQL DECLARE C CURSOR FOR SELECT Item1 FROM T; EXEC SQL DECLARE cur1 CURSOR FOR SE…Examples
ExamplesExamples
EXEC SQL ALLOCATE DESCRIPTOR mydesc; EXEC SQL PREPARE stmt1 FROM :sql_stmt; EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :charvar = NAM…Examples
More results
Find definitions in the PostgreSQL manuals, reference library, and extension catalogue.
pg17: choose a version ex: extensions only ↑↓ select ↵ open