PostgreSQL

https://www.postgresql.org/docs/18/
Admin
PostgreSQL is a powerful, open-source relational database system with advanced features including...
Tokens:2,295,242
Snippets:23,217
Trust Score:10
Update:1 day ago
Show doc for...
Tokens:
Raw
### Common meson setup Options: Prefix, Debug Build, and OpenSSL

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

Examples of passing build options to meson setup: setting a custom installation prefix, generating a debug build, and enabling OpenSSL support. These options can be combined as needed.

```shell
# 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: Build and Install PostgreSQL with Meson

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

A condensed sequence of commands to configure, build, install, and start PostgreSQL using Meson and Ninja. Run these commands from the source directory; the long version is described in the rest of the section.

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

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

Installs HTML and man page documentation.

```shell
make install-docs
```

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

### Build with make or make all

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

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

```bash
make
make all
```

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

### Install documentation tools on macOS with Homebrew

Source: https://www.postgresql.org/docs/18/docguide-toolsets.html

Installs docbook, docbook-xsl, libxslt, and fop. After installation, set XML_CATALOG_FILES as shown in the environment variable examples.

```shell
brew install docbook docbook-xsl libxslt fop
```

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

### Output of the GET DESCRIPTOR example program

Source: https://www.postgresql.org/docs/18/ecpg-sql-get-descriptor.html

When the example program is executed, it prints the column count, column length, and column data as shown.

```text
d_count                 = 1
d_returned_octet_length = 6
d_data                  = testdb
```

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

### Preprocessor input example (foo.pgc)

Source: https://www.postgresql.org/docs/18/ecpg-develop.html

A complete example of a file foo.pgc showing EXEC SQL declarations and a SELECT statement with variable substitution.

```c
EXEC SQL BEGIN DECLARE SECTION;
int index;
int result;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL SELECT res INTO :result FROM mytable WHERE index = :index;
```

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

### Create composite type and table for examples

Source: https://www.postgresql.org/docs/18/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') );
```

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

### Run oid2name example session for database and tablespace lookups

Source: https://www.postgresql.org/docs/18/oid2name.html

Full example session showing oid2name listing databases and tablespaces, resolving filenodes to table names, mixing -t/-f/-x options, and using shell loops with du to report disk space per object. Requires a running server with non-corrupt system catalogs.

```bash
$ # what's in this database server, anyway?
$ oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  17228       alvherre  pg_default
  17255     regression  pg_default
  17227      template0  pg_default
      1      template1  pg_default

$ oid2name -s
All tablespaces:
     Oid  Tablespace Name
-------------------------
    1663       pg_default
    1664        pg_global
  155151         fastdisk
  155152          bigdisk

$ # OK, let's look into database alvherre
$ cd $PGDATA/base/17228

$ # get top 10 db objects in the default tablespace, ordered by size
$ ls -lS * | head -10
-rw-------  1 alvherre alvherre 136536064 sep 14 09:51 155173
-rw-------  1 alvherre alvherre  17965056 sep 14 09:51 1155291
-rw-------  1 alvherre alvherre   1204224 sep 14 09:51 16717
-rw-------  1 alvherre alvherre    581632 sep  6 17:51 1255
-rw-------  1 alvherre alvherre    237568 sep 14 09:50 16674
-rw-------  1 alvherre alvherre    212992 sep 14 09:51 1249
-rw-------  1 alvherre alvherre    204800 sep 14 09:51 16684
-rw-------  1 alvherre alvherre    196608 sep 14 09:50 16700
-rw-------  1 alvherre alvherre    163840 sep 14 09:50 16699
-rw-------  1 alvherre alvherre    122880 sep  6 17:51 16751

$ # What file is 155173?
$ oid2name -d alvherre -f 155173
From database "alvherre":
  Filenode  Table Name
----------------------
    155173    accounts

$ # you can ask for more than one object
$ oid2name -d alvherre -f 155173 -f 1155291
From database "alvherre":
  Filenode     Table Name
-------------------------
    155173       accounts
   1155291  accounts_pkey

$ # you can mix the options, and get more details with -x
$ oid2name -d alvherre -t accounts -f 1155291 -x
From database "alvherre":
  Filenode     Table Name      Oid  Schema  Tablespace
------------------------------------------------------
    155173       accounts   155173  public  pg_default
   1155291  accounts_pkey  1155291  public  pg_default

$ # show disk space for every db object
$ du [0-9]* |
> while read SIZE FILENODE
> do
>   echo "$SIZE       `oid2name -q -d alvherre -i -f $FILENODE`"
> done
16            1155287  branches_pkey
16            1155289  tellers_pkey
17561            1155291  accounts_pkey
...

$ # same, but sort by size
$ du [0-9]* | sort -rn | while read SIZE FN
> do
>   echo "$SIZE   `oid2name -q -d alvherre -f $FN`"
> done
133466             155173    accounts
17561            1155291  accounts_pkey
1177              16717  pg_proc_proname_args_nsp_index
...

$ # If you want to see what's in tablespaces, use the pg_tblspc directory
$ cd $PGDATA/pg_tblspc
$ oid2name -s
All tablespaces:
     Oid  Tablespace Name
-------------------------
    1663       pg_default
    1664        pg_global
  155151         fastdisk
  155152          bigdisk

$ # what databases have objects in tablespace "fastdisk"?
$ ls -d 155151/*
155151/17228/  155151/PG_VERSION

$ # Oh, what was database 17228 again?
$ oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  17228       alvherre  pg_default
  17255     regression  pg_default
  17227      template0  pg_default
      1      template1  pg_default

$ # Let's see what objects does this database have in the tablespace.
$ cd 155151/17228
$ ls -l
total 0
-rw-------  1 postgres postgres 0 sep 13 23:20 155156

$ # OK, this is a pretty small table ... but which one is it?
$ oid2name -d alvherre -f 155156
From database "alvherre":
  Filenode  Table Name
----------------------
    155156         foo
```

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

### PL/pgSQL exception handler using GET STACKED DIAGNOSTICS

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

This example shows how to retrieve error information in an exception handler using GET STACKED DIAGNOSTICS. It assigns MESSAGE_TEXT, PG_EXCEPTION_DETAIL, and PG_EXCEPTION_HINT to variables.

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

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

### Initialize a database cluster with sepgsql

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

This example shows how to set PGDATA, run initdb, edit postgresql.conf to add sepgsql to shared_preload_libraries, and execute sepgsql.sql in template0, template1, and postgres databases. Adjust the 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
```

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

### Extract substring with substring

Source: https://www.postgresql.org/docs/18/functions-bitstring.html

Extracts a substring starting at the start-th bit and stopping after count bits. At least one of start and count must be provided. Example: substring(B'110010111111' from 3 for 2) returns 00.

```sql
substring(B'110010111111' from 3 for 2)
```

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

### Show all settings with SHOW ALL

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

Use SHOW ALL to display every configuration parameter with its name, setting, and description. The output is a multi-row table listing all parameters.

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

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

### GET DIAGNOSTICS Example

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

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

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

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

### Build tutorial files with make

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

Run from the src/tutorial directory to create scripts and compile C files for user-defined functions and types.

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

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

### Basic usage of generate_subscripts

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

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

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

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

### Build and install sepgsql-regtest policy package

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

Run from the contrib/sepgsql directory of a configured PostgreSQL build tree. The Makefile path is an example; locate the appropriate SELinux Makefile 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
```

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

### Create schema, table, and view separately

Source: https://www.postgresql.org/docs/18/sql-createschema.html

Equivalent to the previous example, but using separate commands with fully qualified names.

```sql
CREATE SCHEMA hollywood;
CREATE TABLE hollywood.films (title text, release date, awards text[]);
CREATE VIEW hollywood.winners AS
    SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
```

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

### Run regression tests against an existing installation with make installcheck

Source: https://www.postgresql.org/docs/18/regress-run.html

After installing PostgreSQL and starting the server, run this command to execute the regression tests against the running installation. The tests connect to the local host and default port unless PGHOST and PGPORT are set, and they use a database named 'regression' which will be dropped if it exists.

```shell
make installcheck
```

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

### Example postgresql.conf configuration file

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

Shows a sample postgresql.conf with comments, parameter assignments, and quoting rules. Use as a reference for the file format; parameters set here provide cluster-wide defaults.

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

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

### Example 1: Activate subscription sub1 on subscriber

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

Enables the subscription and refreshes the publication. After this, tables of pub1 start replicating.

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

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

### Basic LIKE Pattern Matching Examples

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

Examples showing how % and _ wildcards work in LIKE patterns.

```sql
'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false
```

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

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

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

Returns the current date and time (start of current transaction) as timestamp with time zone.

```sql
now()
```

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

### Install the plsample extension with CREATE EXTENSION

Source: https://www.postgresql.org/docs/18/sql-createlanguage.html

Users install the language by running CREATE EXTENSION plsample, which executes the extension's creation script.

```sql
CREATE EXTENSION plsample;
```

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

### Asynchronous Notification Example (testlibpq2.c)

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

Demonstrates LISTEN/NOTIFY with libpq. Requires a database populated with the provided SQL commands; 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;
}
```

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

### Get byte length with octet_length

Source: https://www.postgresql.org/docs/18/functions-bitstring.html

Returns the number of bytes in the bit string. Example: octet_length(B'1011111011') returns 2.

```sql
octet_length(B'1011111011')
```

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

### Get bit length with length

Source: https://www.postgresql.org/docs/18/functions-bitstring.html

Returns the number of bits in the bit string. Example: length(B'10111') returns 5.

```sql
length(B'10111')
```

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

### Create a sample table test1

Source: https://www.postgresql.org/docs/18/indexes-intro.html

Creates a table test1 with columns id and content, used as an example for demonstrating index creation.

```sql
CREATE TABLE test1 (
    id integer,
    content varchar
);
```

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

### Get bit length with bit_length

Source: https://www.postgresql.org/docs/18/functions-bitstring.html

Returns the number of bits in the bit string. Example: bit_length(B'10111') returns 5.

```sql
bit_length(B'10111')
```

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

### Install postgres_fdw extension

Source: https://www.postgresql.org/docs/18/postgres-fdw.html

First step in setting up postgres_fdw: install the extension in the local database.

```sql
CREATE EXTENSION postgres_fdw;
```

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

### Output of transaction count DTrace script

Source: https://www.postgresql.org/docs/18/dynamic-trace.html

Example output showing counts of starts and commits and total commit time in nanoseconds.

```text
# ./txn_count.d `pgrep -n postgres` or ./txn_count.d <PID>
^C

Start                                          71
Commit                                         70
Total time (ns)                        2312105013
```

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

### Create database demo with host, port, and template0

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

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

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

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

### LogicalDecodeBeginCB

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

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

```APIDOC
## LogicalDecodeBeginCB

### 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
Callback function

### 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.
```
Tokens:
Raw