PostgreSQL

https://www.postgresql.org/docs/devel/
Admin
PostgreSQL is a powerful, open-source object-relational database management system used for reliably...
Tokens:2,226,166
Snippets:18,747
Trust Score:10
Update:1 month ago
Show doc for...
Tokens:
Raw
### meson setup with common build options

Source: https://www.postgresql.org/docs/devel/install-meson.html

Shows how to pass different build options to meson setup: a custom prefix, a debug build, and OpenSSL support. These are examples of common configuration choices.

```bash
# configure with a different installation prefix
meson setup build --prefix=/home/user/pg-install

# configure to generate a debug build
meson setup build --buildtype=debug

# configure to build with OpenSSL support
meson setup build -Dssl=openssl

```

--------------------------------

### Create tables for replication examples

Source: https://www.postgresql.org/docs/devel/logical-replication-row-filter.html

Initial setup of tables t1, t2, and t3 with primary keys to be used in subsequent replication examples.

```sql
/* pub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
/* pub # */ CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
/* pub # */ CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
```

--------------------------------

### Start psql with testdb database

Source: https://www.postgresql.org/docs/devel/app-psql.html

Example of starting psql with the database name testdb, showing the version banner and the prompt testdb=>. The prompt indicates the current database and that psql is ready for input.

```shell
$ **psql testdb**
psql (20devel)
Type "help" for help.

testdb=>

```

--------------------------------

### CREATE TYPE and CREATE EXTENSION Setup

Source: https://www.postgresql.org/docs/devel/sql-createtransform.html

Sets up the data type hstore and installs the procedural language extension plpython3u as the first step of the example transform.

```sql
CREATE TYPE hstore ...;

CREATE EXTENSION plpython3u;

```

--------------------------------

### SHOW ALL example

Source: https://www.postgresql.org/docs/devel/sql-show.html

Example output for SHOW ALL. The command displays all configuration parameters with their settings and descriptions, returning 196 rows in this example.

```sql
SHOW ALL;
            name         | setting |                description
-------------------------+---------+-------------------------------------------------
 allow_system_table_mods | off     | Allows modifications of the structure of ...
    .
    .
    .
 xmloption               | content | Sets whether XML data in implicit parsing ...
 zero_damaged_pages      | off     | Continues processing past damaged page headers.
(196 rows)
```

--------------------------------

### Install Documentation

Source: https://www.postgresql.org/docs/devel/install-make.html

Installs HTML and man pages for PostgreSQL.

```bash
**make install-docs**
```

--------------------------------

### GET DIAGNOSTICS Example

Source: https://www.postgresql.org/docs/devel/plpgsql-statements.html

An example of assigning the ROW_COUNT status item to an integer variable.

```plpgsql
GET DIAGNOSTICS integer_var = ROW_COUNT;
```

--------------------------------

### Start psql and run tutorial script

Source: https://www.postgresql.org/docs/devel/tutorial-sql-intro.html

Launch psql in single step mode for the database mydb and execute the basics.sql tutorial file.

```bash
$ **psql -s mydb**

...

mydb=> **\i basics.sql**
```

--------------------------------

### Setup Composite Type and Table

Source: https://www.postgresql.org/docs/devel/ecpg-variables.html

Initial SQL setup for the composite type and table used in the subsequent ECPG examples.

```sql
CREATE TYPE comp_t AS (intval integer, textval varchar(32));
CREATE TABLE t4 (compval comp_t);
INSERT INTO t4 VALUES ( (256, 'PostgreSQL') );
```

--------------------------------

### regexp_count Function Examples

Source: https://www.postgresql.org/docs/devel/functions-matching.html

Examples of the regexp_count function: the first counts matches from the start, the second uses start=1 and the 'i' flag for case-insensitive matching.

```sql
regexp_count('ABCABCAXYaxy', 'A.')          _3_
```

```sql
regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  _4_
```

--------------------------------

### Install World

Source: https://www.postgresql.org/docs/devel/install-make.html

Installs the entire build including documentation.

```bash
**make install-world**
```

--------------------------------

### Short version: quick build and install commands

Source: https://www.postgresql.org/docs/devel/install-meson.html

Complete sequence to build, install, and initialize a PostgreSQL database with default settings. Run as root where shown (su, adduser, mkdir, chown) to set up the data directory and system user.

```bash
meson setup build --prefix=/usr/local/pgsql
cd build
ninja
su
ninja install
adduser postgres
mkdir -p /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

```

--------------------------------

### Short Version Installation

Source: https://www.postgresql.org/docs/devel/install-make.html

A quick sequence of commands to configure, build, install, and initialize a PostgreSQL instance.

```bash
./configure
make
su
make install
adduser postgres
mkdir -p /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
```

--------------------------------

### Standard SQL Comment

Source: https://www.postgresql.org/docs/devel/sql-syntax-lexical.html

Example of a single-line comment starting with double dashes.

```sql
-- This is a standard SQL comment
```

--------------------------------

### raise_pay using array get for composite result

Source: https://www.postgresql.org/docs/devel/pltcl-functions.html

Tip example showing how to build a result list from an array representation of the desired tuple using the Tcl array get command.

```sql
CREATE FUNCTION raise_pay(employee, delta int) RETURNS employee AS $$
    set 1(salary) [expr {$1(salary) + $2}]
    return [array get 1]
$$ LANGUAGE pltcl;

```

--------------------------------

### PREPARE example with host variable and descriptor

Source: https://www.postgresql.org/docs/devel/ecpg-sql-prepare.html

Example showing how to prepare a statement from a host variable and execute it using SQL descriptors. The statement uses question marks for parameters.

```c
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 INTO SQL DESCRIPTOR outdesc;

```

--------------------------------

### Example 1: Activate subscription

Source: https://www.postgresql.org/docs/devel/logical-replication-subscription.html

On the subscriber, enables the subscription and refreshes the publication to start replication.

```sql
/* sub # */ ALTER SUBSCRIPTION sub1 ENABLE;
/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;

```

--------------------------------

### \connect examples

Source: https://www.postgresql.org/docs/devel/app-psql.html

Shows various forms of the \connect command: positional parameters, a conninfo string, a service name, reusing previous parameters with -reuse-previous=on, and a URI connection string.

```sql
=> \c mydb myuser host.dom 6432
=> \c service=foo
=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
=> \c -reuse-previous=on sslmode=require    -- changes only sslmode
=> \c postgresql://tom@localhost/mydb?application_name=myapp

```

--------------------------------

### Build Everything Including Documentation

Source: https://www.postgresql.org/docs/devel/install-make.html

Command to build the server, utilities, client applications, documentation, and contrib modules.

```bash
make world
```

--------------------------------

### Start Build

Source: https://www.postgresql.org/docs/devel/install-make.html

Commands to initiate the build process using GNU make.

```bash
make
make all
```

--------------------------------

### dblink example: query remote pg_proc using default connection

Source: https://www.postgresql.org/docs/devel/contrib-dblink-function.html

Perform a query on a remote database using dblink after a dblink_connect has been established. The example filters pg_proc for procedures with names starting with 'bytea'.

```sql
SELECT * FROM dblink('SELECT proname, prosrc FROM pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

```

--------------------------------

### dblink example: query remote pg_proc with search_path

Source: https://www.postgresql.org/docs/devel/contrib-dblink-function.html

Perform a query on a remote database using dblink with a connection string that sets the search_path. The example filters pg_proc for procedures with names starting with 'bytea'.

```sql
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
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

```

--------------------------------

### EXPLAIN with pg_plan_advice feedback

Source: https://www.postgresql.org/docs/devel/pgplanadvice.html

Example showing how to set advice and view the resulting feedback in the EXPLAIN output.

```sql
SET pg_plan_advice.advice = 'hash_join(f g) join_order(f g) index_scan(f no_such_index)';
EXPLAIN (COSTS OFF)
    SELECT * FROM jo_fact f
    LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
    LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
    WHERE val1 = 1 AND val2 = 1;
                            QUERY PLAN
-------------------------------------------------------------------
 Hash Join
   Hash Cond: ((d1.id = f.dim1_id) AND (d2.id = f.dim2_id))
   ->  Nested Loop
         ->  Seq Scan on jo_dim2 d2
               Filter: (val2 = 1)
         ->  Materialize
               ->  Seq Scan on jo_dim1 d1
                     Filter: (val1 = 1)
   ->  Hash
         ->  Seq Scan on jo_fact f
 Supplied Plan Advice:
   INDEX_SCAN(f no_such_index) /* matched, inapplicable, failed */
   HASH_JOIN(f) /* matched */
   HASH_JOIN(g) /* not matched */
   JOIN_ORDER(f g) /* partially matched */
```

--------------------------------

### Obtaining Error Information with GET STACKED DIAGNOSTICS

Source: https://www.postgresql.org/docs/devel/plpgsql-control-structures.html

An example showing how to capture error details like the message text, detail, and hint within an exception block using GET STACKED DIAGNOSTICS.

```plpgsql
DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- some processing which might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;
```

--------------------------------

### regexp_split_to_table example with zero-length matches

Source: https://www.postgresql.org/docs/devel/functions-matching.html

Example using the pattern '\s*' (zero or more whitespace). Demonstrates that zero-length matches at the start or end of the string or immediately after a previous match are ignored, resulting in each character as a separate row.

```sql
SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
 foo
-----
 t
 h
 e
 q
 u
 i
 c
 k
 b
 r
 o
 w
 n
 f
 o
 x
(16 rows)
```

--------------------------------

### Set up example roles with INHERIT and SET options

Source: https://www.postgresql.org/docs/devel/role-membership.html

Sets up example roles and memberships to illustrate inheritance and SET behavior. After connecting as joe, privileges from admin and island are inherited, but not from wheel because the membership via admin uses WITH INHERIT FALSE.

```sql
CREATE ROLE joe LOGIN;
CREATE ROLE admin;
CREATE ROLE wheel;
CREATE ROLE island;
GRANT admin TO joe WITH INHERIT TRUE;
GRANT wheel TO admin WITH INHERIT FALSE;
GRANT island TO joe WITH INHERIT TRUE, SET FALSE;
```

--------------------------------

### dblink example: query remote pg_proc using named connection

Source: https://www.postgresql.org/docs/devel/contrib-dblink-function.html

Perform a query on a remote database using dblink with the named connection 'myconn'. The example filters pg_proc for procedures with names starting with 'bytea'.

```sql
SELECT * FROM dblink('myconn', 'SELECT proname, prosrc FROM pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 bytearecv  | bytearecv
 byteasend  | byteasend
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteain    | byteain
 byteaout   | byteaout
(14 rows)

```

--------------------------------

### Reconfigure with meson configure

Source: https://www.postgresql.org/docs/devel/install-meson.html

Use to change build configuration after the initial setup. Example enables assert assertions.

```bash
meson configure -Dcassert=true

```

--------------------------------

### SHOW geqo example

Source: https://www.postgresql.org/docs/devel/sql-show.html

Example output for SHOW geqo. The command displays the current setting of the geqo parameter, which is on in this example.

```sql
SHOW geqo;
 geqo
------
 on
(1 row)
```

--------------------------------

### Bad error message example

Source: https://www.postgresql.org/docs/devel/error-style-guide.html

Example of a poorly formatted primary error message that includes implementation details and a long addendum. The style guide recommends replacing it with a short primary message, a detail message, and a hint message.

```text
IpcMemoryCreate: shmget(key=%d, size=%u, 0%o) failed: %m
(plus a long addendum that is basically a hint)
```

--------------------------------

### Create data directory with proper ownership and run initdb

Source: https://www.postgresql.org/docs/devel/creating-cluster.html

Shows the recommended process of creating the parent directory, setting ownership to the postgres user, switching to that user, and running initdb. Required when the parent directory does not exist and the PostgreSQL user lacks write permissions to it.

```shell
root# **mkdir /usr/local/pgsql**
root# **chown postgres /usr/local/pgsql**
root# **su postgres**
postgres$ **initdb -D /usr/local/pgsql/data**
```

--------------------------------

### Create and populate my_films table

Source: https://www.postgresql.org/docs/devel/functions-json.html

Initial setup of the my_films table containing JSON data used in subsequent examples.

```sql
CREATE TABLE my_films ( js jsonb );

INSERT INTO my_films VALUES (
'{ "favorites" : [
   { "kind" : "comedy", "films" : [
     { "title" : "Bananas",
       "director" : "Woody Allen"},
     { "title" : "The Dinner Game",
       "director" : "Francis Veber" } ] },
   { "kind" : "horror", "films" : [
     { "title" : "Psycho",
       "director" : "Alfred Hitchcock" } ] },
   { "kind" : "thriller", "films" : [
     { "title" : "Vertigo",
       "director" : "Alfred Hitchcock" } ] },
   { "kind" : "drama", "films" : [
     { "title" : "Yojimbo",
       "director" : "Akira Kurosawa" } ] }
  ] }');
```

--------------------------------

### FETCH examples

Source: https://www.postgresql.org/docs/devel/plpgsql-cursors.html

Examples of using the FETCH command with various directions and targets.

```sql
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;
```

--------------------------------

### POSIX Regular Expression Matching Examples

Source: https://www.postgresql.org/docs/devel/functions-matching.html

Examples showing how POSIX regular expressions match anywhere within a string unless anchored. The dot matches any character, * repeats the preceding item, | means OR, parentheses group, and ^ anchors to the start.

```sql
'abcd' ~ 'bc'     _true_
```

```sql
'abcd' ~ 'a.c'    _true — dot matches any character_
```

```sql
'abcd' ~ 'a.*d'   _true — * repeats the preceding pattern item_
```

```sql
'abcd' ~ '(b|x)'  _true — | means OR, parentheses group_
```

```sql
'abcd' ~ '^a'     _true — ^ anchors to start of string_
```

```sql
'abcd' ~ '^(b|c)' _false — would match except for anchoring_
```

--------------------------------

### Start server (pg_ctl start)

Source: https://www.postgresql.org/docs/devel/app-pg-ctl.html

Starts the server and waits until it is accepting connections.

```shell
$ **pg_ctl start**
```

--------------------------------

### Example postgresql.conf content

Source: https://www.postgresql.org/docs/devel/config-setting.html

Shows a minimal example of a postgresql.conf file with comments, parameter assignments, and a quoted value. One parameter per line; '=' is optional; whitespace insignificant; '#' starts a comment; non-identifier values must be single-quoted.

```plaintext
# This is a comment
log_connections = all
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB

```

--------------------------------

### Managing Multiple Connections Example

Source: https://www.postgresql.org/docs/devel/ecpg-connect.html

A complete C program demonstrating how to open, switch, and query multiple database connections.

```c
#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
    char dbname[1024];
EXEC SQL END DECLARE SECTION;

int
main()
{
    EXEC SQL CONNECT TO testdb1 AS con1 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
    EXEC SQL CONNECT TO testdb2 AS con2 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
    EXEC SQL CONNECT TO testdb3 AS con3 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

    /* This query would be executed in the last opened database "testdb3". */
    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb3)\n", dbname);

    /* Using "AT" to run a query in "testdb2" */
    EXEC SQL AT con2 SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb2)\n", dbname);

    /* Switch the current connection to "testdb1". */
    EXEC SQL SET CONNECTION con1;

    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb1)\n", dbname);

    EXEC SQL DISCONNECT ALL;
    return 0;
}
```

```text
current=testdb3 (should be testdb3)
current=testdb2 (should be testdb2)
current=testdb1 (should be testdb1)
```

--------------------------------

### Get multiple WAL records info with pg_get_wal_records_info

Source: https://www.postgresql.org/docs/devel/pgwalinspect.html

Retrieves information for all valid WAL records between the specified start and end LSNs.

```sql
postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;
-[ RECORD 1 ]----+--------------------------------------------------------------
start_lsn        | 0/1E913618
end_lsn          | 0/1E913650
prev_lsn         | 0/1E9135A0
xid              | 0
resource_manager | Standby
record_type      | RUNNING_XACTS
record_length    | 50
main_data_length | 24
fpi_length       | 0
description      | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 33775
block_ref        |
```
Tokens:
Raw