PostgreSQL

https://www.postgresql.org/docs/17/
Admin
PostgreSQL is a powerful, open-source relational database system that supports advanced SQL...
Tokens:2,261,282
Snippets:23,143
Trust Score:10
Update:1 day ago
Show doc for...
Tokens:
Raw
### meson setup with Common Options

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

Examples of passing build options to meson setup: setting a custom installation prefix, enabling a debug build, and enabling OpenSSL support.

```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
```

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

### Short Version Installation Commands

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

A quick sequence of commands to configure, build, and install PostgreSQL, then initialize a database cluster and start the server. Run as root where indicated (su) and as the postgres user for initdb and pg_ctl.

```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
```

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

### Quick Installation with Meson

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

A complete sequence of commands to configure, build, install, and start PostgreSQL using Meson and Ninja. Run these commands from the source directory.

```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
```

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

### Install PostgreSQL with ninja install

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

Run after building PostgreSQL to install files into the directories specified during meson setup. Ensure you have appropriate write permissions, potentially requiring root.

```shell
ninja install
```

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

### Example 1: Enable and refresh subscription sub1

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

Enables the subscription and refreshes it to start replicating tables from pub1. Run these commands on the subscriber after manually creating the slot.

```sql
test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
ALTER SUBSCRIPTION
test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
ALTER SUBSCRIPTION
```

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

### Build with make or make all

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

Start the build using GNU make. Both commands are equivalent for a standard build.

```bash
make
make all
```

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

### PL/pgSQL example using GET STACKED DIAGNOSTICS

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

This example retrieves the exception's message text, detail, and hint into variables using GET STACKED DIAGNOSTICS within an exception handler.

```sql
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;
```

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

### Show all settings with SHOW ALL

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

Use SHOW ALL to display all configuration parameters with their name, setting, and description. The output is truncated in the example and reports 196 rows.

```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 with make install-docs

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

Installs the HTML and man page documentation.

```shell
make install-docs
```

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

### bt_multi_page_stats example

Source: https://www.postgresql.org/docs/17/pageinspect.html

Returns the same information as bt_page_stats for each page in a range. The example shows output for two pages starting at block 5 of pg_proc_oid_index.

```sql
test=# SELECT * FROM bt_multi_page_stats('pg_proc_oid_index', 5, 2);
-[ RECORD 1 ]-+-----
blkno         | 5
type          | l
live_items    | 367
dead_items    | 0
avg_item_size | 16
page_size     | 8192
free_size     | 808
btpo_prev     | 4
btpo_next     | 6
btpo_level    | 0
btpo_flags    | 1
-[ RECORD 2 ]-+-----
blkno         | 6
type          | l
live_items    | 367
dead_items    | 0
avg_item_size | 16
page_size     | 8192
free_size     | 808
btpo_prev     | 5
btpo_next     | 7
btpo_level    | 0
btpo_flags    | 1
```

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

### Install world with make install-world

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

Use this if you built the world above; it also installs the documentation.

```shell
make install-world
```

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

### Create ascending sequence serial starting at 101

Source: https://www.postgresql.org/docs/17/sql-createsequence.html

Creates a sequence named serial with a start value of 101. Use as the initial setup before calling nextval('serial').

```sql
CREATE SEQUENCE serial START 101;
```

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

### Compile tutorial files with make

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

Run this in the src/tutorial directory to create scripts and compile C files for user-defined functions and types. Requires the PostgreSQL source distribution.

```shell
$ cd .../src/tutorial
$ make
```

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

### Create composite type and table for examples

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

Defines a composite type comp_t and a table t4, then inserts a sample row. Use this setup before running the 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') );
```

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

### Initialize a fresh database cluster with sepgsql

Source: https://www.postgresql.org/docs/17/sepgsql.html

This example shows how to set up a new PostgreSQL cluster with sepgsql loaded and initialized. It sets PGDATA, runs initdb, edits postgresql.conf to add sepgsql to shared_preload_libraries, and then runs sepgsql.sql in template0, template1, and postgres databases using single-user mode. Adjust paths as needed for your installation.

```shell
$ export PGDATA=/path/to/data/directory
$ initdb
$ vi $PGDATA/postgresql.conf
  change
    #shared_preload_libraries = ''                # (change requires restart)
  to
    shared_preload_libraries = 'sepgsql'          # (change requires restart)
$ for DBNAME in template0 template1 postgres; do
    postgres --single -F -c exit_on_error=true $DBNAME \
      </usr/local/pgsql/share/contrib/sepgsql.sql >/dev/null
  done
```

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

### GET DIAGNOSTICS Example

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

Assigns the number of rows processed by the most recent SQL command to integer_var.

```sql
GET DIAGNOSTICS integer_var = ROW_COUNT;
```

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

### Install world without documentation with make install-world-bin

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

Use this if you built the world without the documentation above.

```shell
make install-world-bin
```

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

### Get current date and time with now()

Source: https://www.postgresql.org/docs/17/functions-datetime.html

Returns the current date and time (start of current transaction).

```sql
now()
```

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

### Get current date and time with transaction_timestamp()

Source: https://www.postgresql.org/docs/17/functions-datetime.html

Returns the current date and time (start of current transaction).

```sql
transaction_timestamp()
```

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

### Example program managing multiple connections

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

Connects to three databases, demonstrates default connection, AT clause, and SET CONNECTION. Output shows current database after each operation.

```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;
}
```

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

### psql \c and \connect examples

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

Examples of using \c to connect with positional parameters, service name, conninfo string, reuse-previous option, and URI. Each line is a separate invocation.

```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
```

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

### Get current date and time with statement_timestamp()

Source: https://www.postgresql.org/docs/17/functions-datetime.html

Returns the current date and time (start of current statement).

```sql
statement_timestamp()
```

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

### Configure with Default Options

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

Run the configure script with default options to set up the build tree for a default installation.

```bash
./configure
```

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

### regexp_replace examples

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

Examples of regexp_replace with various patterns, replacements, and flags. The first three show basic replacement with and without the 'g' flag and backreferences. The last two demonstrate the start and N parameters with the 'i' flag.

```sql
regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')
                                   fooXarYXazY
regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i')
                                   X PXstgrXSQL fXnctXXn
regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i')
                                   A PostgrXSQL function
```

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

### Create database demo with host, port, and template options

Source: https://www.postgresql.org/docs/17/app-createdb.html

Creates the database demo on host eden, port 5000, using template0. The -e flag echoes the underlying SQL command.

```shell
$ createdb -p 5000 -h eden -T template0 -e demo
CREATE DATABASE demo TEMPLATE template0;
```

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

### Build and install sepgsql-regtest policy package

Source: https://www.postgresql.org/docs/17/sepgsql.html

Run these commands in the contrib/sepgsql directory of a configured PostgreSQL build tree. The Makefile path is an example; locate the appropriate one on your system. After installation, semodule -l should list sepgsql-regtest.

```bash
$ cd .../contrib/sepgsql
$ make -f /usr/share/selinux/devel/Makefile
$ sudo semodule -u sepgsql-regtest.pp
$ sudo semodule -l | grep sepgsql
sepgsql-regtest 1.07
```

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

### Example table of contents listing file

Source: https://www.postgresql.org/docs/17/app-pgrestore.html

Shows the format of a pg_restore -l output file, with a header and one line per archive item. Semicolons start comments, and numbers at line starts are internal archive IDs.

```text
;
; Archive created at Mon Sep 14 13:55:39 2009
;     dbname: DBDEMOS
;     TOC Entries: 81
;     Compression: 9
;     Dump Version: 1.10-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 8.3.5
;     Dumped by pg_dump version: 8.3.8
;
;
; Selected TOC Entries:
;
3; 2615 2200 SCHEMA - public pasha
1861; 0 0 COMMENT - SCHEMA public pasha
1862; 0 0 ACL - public pasha
317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha
```

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

### Start PostgreSQL server with postgres command

Source: https://www.postgresql.org/docs/17/upgrading.html

Start the new database server using the postgres command. Run this as the special database user account.

```bash
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
```

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

### psql welcome message and prompt

Source: https://www.postgresql.org/docs/17/tutorial-accessdb.html

After starting psql, you will see the version and a help hint, followed by the prompt mydb=> indicating psql is ready for SQL input.

```text
psql (17.11)
Type "help" for help.

mydb=>
```

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

### enum_range with null first argument example

Source: https://www.postgresql.org/docs/17/functions-enum.html

When the first parameter is null, the result starts with the first value of the enum type.

```sql
enum_range(NULL, 'green'::rainbow)
```

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

### begin_cb

Source: https://www.postgresql.org/docs/17/logicaldecoding-output-plugin.html

Required callback called when the start of a committed transaction has been decoded. Aborted transactions and their contents never get decoded.

```APIDOC
## begin_cb

### Description
The required `begin_cb` callback is called whenever a start of a committed transaction has been decoded. Aborted transactions and their contents never get decoded.

### Method
C function pointer

### Signature
```c
typedef void (*LogicalDecodeBeginCB) (struct LogicalDecodingContext *ctx,
                                      ReorderBufferTXN *txn);
```

### Parameters
- **ctx** (struct LogicalDecodingContext *) - Logical decoding context.
- **txn** (ReorderBufferTXN *) - Contains meta information about the transaction, like the time stamp at which it has been committed and its XID.
```

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

### Asynchronous Notification Example (testlibpq2.c)

Source: https://www.postgresql.org/docs/17/libpq-example.html

Demonstrates LISTEN/NOTIFY using libpq. Start the program, then from psql execute NOTIFY TBL2 or insert into TESTLIBPQ2.TBL1 to trigger notifications. The program exits after receiving four notifications.

```c
/*
 * src/test/examples/testlibpq2.c
 *
 *
 * testlibpq2.c
 *      Test of the asynchronous notification interface
 *
 * Start this program, then from psql in another window do
 *   NOTIFY TBL2;
 * Repeat four times to get this program to exit.
 *
 * Or, if you want to get fancy, try this:
 * populate a database with the following commands
 * (provided in src/test/examples/testlibpq2.sql):
 *
 *   CREATE SCHEMA TESTLIBPQ2;
 *   SET search_path = TESTLIBPQ2;
 *   CREATE TABLE TBL1 (i int4);
 *   CREATE TABLE TBL2 (i int4);
 *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
 *     (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
 *
 * Start this program, then from psql do this four times:
 *
 *   INSERT INTO TESTLIBPQ2.TBL1 VALUES (10);
 */

#ifdef WIN32
#include <windows.h>
#endif
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/select.h>
#include <sys/time.h>
#include <sys/types.h>

#include "libpq-fe.h"

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    PGnotify   *notify;
    int         nnotifies;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "%s", PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /* Set always-secure search path, so malicious users can't take control. */
    res = PQexec(conn,
                 "SELECT pg_catalog.set_config('search_path', '', false)");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /*
     * Should PQclear PGresult whenever it is no longer needed to avoid memory
     * leaks
     */
    PQclear(res);

    /*
     * Issue LISTEN command to enable notifications from the rule's NOTIFY.
     */
    res = PQexec(conn, "LISTEN TBL2");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    /* Quit after four notifies are received. */
    nnotifies = 0;
    while (nnotifies < 4)
    {
        /*
         * Sleep until something happens on the connection.  We use select(2)
         * to wait for input, but you could also use poll() or similar
         * facilities.
         */
        int         sock;
        fd_set      input_mask;

        sock = PQsocket(conn);

        if (sock < 0)
            break;              /* shouldn't happen */

        FD_ZERO(&input_mask);
        FD_SET(sock, &input_mask);

        if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
        {
            fprintf(stderr, "select() failed: %s\n", strerror(errno));
            exit_nicely(conn);
        }

        /* Now check for input */
        PQconsumeInput(conn);
        while ((notify = PQnotifies(conn)) != NULL)
        {
            fprintf(stderr,
                    "ASYNC NOTIFY of '%s' received from backend PID %d\n",
                    notify->relname, notify->be_pid);
            PQfreemem(notify);
            nnotifies++;
            PQconsumeInput(conn);
        }
    }

    fprintf(stderr, "Done.\n");

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}
```

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

### Sample session using execq

Source: https://www.postgresql.org/docs/17/spi-examples.html

Demonstrates the behavior of execq, including row processing, visibility rules, and the effect of the row count argument.

```sql
=> SELECT execq('CREATE TABLE a (x integer)', 0);
 execq
-------
     0
(1 row)

=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
INSERT 0 1
=> SELECT execq('SELECT * FROM a', 0);
INFO:  EXECQ:  0    -- inserted by execq
INFO:  EXECQ:  1    -- returned by execq and inserted by upper INSERT

 execq
-------
     2
(1 row)

=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a RETURNING *', 1);
INFO:  EXECQ:  2    -- 0 + 2, then execution was stopped by count
 execq
-------
     1
(1 row)

=> SELECT execq('SELECT * FROM a', 10);
INFO:  EXECQ:  0
INFO:  EXECQ:  1
INFO:  EXECQ:  2

 execq
-------
     3              -- 10 is the max value only, 3 is the real number of rows
(1 row)

=> SELECT execq('INSERT INTO a SELECT x + 10 FROM a', 1);
 execq
-------
     3              -- all rows processed; count does not stop it, because nothing is returned
(1 row)

=> SELECT * FROM a;
 x
----
  0
  1
  2
 10
 11
 12
(6 rows)

=> DELETE FROM a;
DELETE 6
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INSERT 0 1
=> SELECT * FROM a;
 x
---
 1                  -- 0 (no rows in a) + 1
(1 row)

=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INFO:  EXECQ:  1
INSERT 0 1
=> SELECT * FROM a;
 x
---
 1
 2                  -- 1 (there was one row in a) + 1
(2 rows)

-- This demonstrates the data changes visibility rule.
-- execq is called twice and sees different numbers of rows each time:

=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
INFO:  EXECQ:  1    -- results from first execq
INFO:  EXECQ:  2
INFO:  EXECQ:  1    -- results from second execq
INFO:  EXECQ:  2
INFO:  EXECQ:  2
INSERT 0 2
=> SELECT * FROM a;
 x
---
 1
 2
 2                  -- 2 rows * 1 (x in first row)
 6                  -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
(4 rows)
```

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

### Basic usage of generate_subscripts

Source: https://www.postgresql.org/docs/17/functions-srf.html

Use generate_subscripts to get the subscripts of an array. The example shows the subscripts for a one-dimensional integer array.

```sql
-- basic usage:
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
 s
---
 1
 2
 3
 4
(4 rows)
```
Tokens:
Raw