### meson setup with Common Options

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

Examples of configuring the build with different installation prefix, build type, and OpenSSL support. Each command is a separate configuration choice.

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

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

### Start psql and show prompt

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

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

```shell
$ psql testdb
psql (18.6)
Type "help" for help.

testdb=>
```

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

### Set up type and language for CREATE TRANSFORM

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

First step in the example: create the hstore type and install the plpython3u extension.

```sql
CREATE TYPE hstore ...;

CREATE EXTENSION plpython3u;
```

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

### Short Version Installation Commands

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

Quick installation commands for PostgreSQL using Autoconf and Make. Includes configuration, build, installation, and initial database setup.

```shell
./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
```

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

### date_bin examples with results

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

Examples of date_bin with different origins, showing the resulting bin start times.

```sql
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00
```

```sql
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30
```

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

### make install-docs - Install documentation

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

Installs the HTML and man page documentation.

```shell
make install-docs
```

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

### Setup file_fdw example

Source: https://www.postgresql.org/docs/current/rules-materializedviews.html

Sets up a foreign table using file_fdw and creates a materialized view with indexes and pg_trgm extension for faster access.

```sql
CREATE EXTENSION file_fdw;
CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE words (word text NOT NULL)
  SERVER local_file
  OPTIONS (filename '/usr/share/dict/words');
CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
CREATE UNIQUE INDEX wrd_word ON wrd (word);
CREATE EXTENSION pg_trgm;
CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
VACUUM ANALYZE wrd;
```

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

### Example 1: Activate subscription on subscriber

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

Enables the subscription and refreshes the publication to start replication.

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

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

### Original Query on shoe_ready View

Source: https://www.postgresql.org/docs/current/rules-views.html

Initial query selecting from the shoe_ready view with a filter on total_avail. This is the starting point for the rewrite system example.

```sql
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM shoe_ready shoe_ready
 WHERE shoe_ready.total_avail >= 2;
```

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

### Install PostgreSQL with ninja install

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

Run this command after building PostgreSQL with Meson to install files into the directories specified during configuration. Requires appropriate write permissions, possibly as root. For more options, use meson install instead.

```shell
ninja install
```

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

### Install new PostgreSQL binaries with custom prefix

Source: https://www.postgresql.org/docs/current/pgupgrade.html

Install the new server's binaries to a custom location using the prefix variable for source installs.

```bash
make prefix=/usr/local/pgsql.new install
```

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

### Build tutorial examples with make

Source: https://www.postgresql.org/docs/current/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
```

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

### pg_get_wal_records_info example

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

Shows how to call pg_get_wal_records_info with start and end LSNs to get one row per valid WAL record in that range. The example uses LIMIT 1 to show a single record. The function raises an error if start_lsn is not available.

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

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

### GET DIAGNOSTICS example with ROW_COUNT

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

Example of using GET DIAGNOSTICS to assign the number of rows processed by the most recent SQL command to a variable.

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

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

### pg_get_wal_record_info example

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

Shows how to call pg_get_wal_record_info with an LSN to get information about the WAL record at or after that LSN. The output includes fields like start_lsn, end_lsn, resource_manager, and record_type. If the LSN is not at the start of a record, the next valid record is shown; if none exists, an error is raised.

```sql
postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');
-[ RECORD 1 ]----+-------------------------------------------------
start_lsn        | 0/E419E28
end_lsn          | 0/E419E68
prev_lsn         | 0/E419D78
xid              | 0
resource_manager | Heap2
record_type      | VACUUM
record_length    | 58
main_data_length | 2
fpi_length       | 0
description      | nunused: 5, unused: [1, 2, 3, 4, 5]
block_ref        | blkref #0: rel 1663/16385/1249 fork main blk 364
```

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

### EXEC SQL DEFINE examples

Source: https://www.postgresql.org/docs/current/ecpg-preproc.html

Examples of defining a feature flag and constants.

```ecpg
EXEC SQL DEFINE HAVE_FEATURE;
```

```ecpg
EXEC SQL DEFINE MYNUMBER 12;
EXEC SQL DEFINE MYSTRING 'abc';
```

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

### make install - Install PostgreSQL

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

Installs PostgreSQL into the directories specified during configuration. Requires appropriate write permissions, typically run as root.

```shell
make install
```

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

### libpq Example Program 1: Connect, Query, and Fetch with Cursor

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

This C program demonstrates a complete libpq workflow: connecting to a database, setting a secure search path, starting a transaction, declaring a cursor, fetching rows, and cleaning up. It uses PQconnectdb, PQexec, PQresultStatus, PQclear, and PQfinish. The program accepts an optional conninfo string as a command-line argument; otherwise it defaults to 'dbname = postgres'. It prints attribute names and row values from the pg_database catalog.

```c
/*
 * src/test/examples/testlibpq.c
 *
 *
 * testlibpq.c
 *
 *      Test the C version of libpq, the PostgreSQL frontend library.
 */
#include <stdio.h>
#include <stdlib.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;
    int         nFields;
    int         i,
                j;

    /*
     * 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);

    /*
     * Our test case here involves using a cursor, for which we must be inside
     * a transaction block.  We could do the whole thing with a single
     * PQexec() of "select * from pg_database", but that's too trivial to make
     * a good example.
     */

    /* Start a transaction block */
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    /*
     * Fetch rows from pg_database, the system catalog of databases
     */
    res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in myportal");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /* first, print out the attribute names */
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");

    /* next, print out the rows */
    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }

    PQclear(res);

    /* close the portal ... we don't bother to check for errors ... */
    res = PQexec(conn, "CLOSE myportal");
    PQclear(res);

    /* end the transaction */
    res = PQexec(conn, "END");
    PQclear(res);

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

    return 0;
}

```

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

### Create database demo with default server

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

Creates the database 'demo' using the default database server.

```shell
$ createdb demo
```

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

### PL/pgSQL example using GET DIAGNOSTICS PG_CONTEXT

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

Shows how to capture the call stack with GET DIAGNOSTICS stack = PG_CONTEXT and raise it as a NOTICE. The example defines outer_func and inner_func, then calls outer_func to display the stack trace.

```sql
CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
  RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
  stack text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE E'--- Call Stack ---\n%', stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Call Stack ---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
 ------------
           1
(1 row)
```

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

### Build Commands

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

Commands to start the build. Use 'make' or 'make all' to build the server and utilities. Remember to use GNU make.

```shell
make
make all
```

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

### regexp_instr examples

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

Returns the starting or ending position of the N'th match. The second example uses named parameters and the 'i' flag.

```sql
regexp_instr('number of your street, town zip, FR', '[^,]+', 1, 2)
                                   23
regexp_instr(string=>'ABCDEFGHI', pattern=>'(c..)(...)', start=>1, "N"=>1, endoption=>0, flags=>'i', subexpr=>2)
                                   6
```

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

### SHOW ALL - Display All Settings

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

Lists all configuration parameters and their current settings. The output is truncated in the example, showing a subset of the 196 rows returned.

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

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

### pgstatindex example query

Source: https://www.postgresql.org/docs/current/pgstattuple.html

Example of using pgstatindex to get B-tree index statistics. The output shows version, tree_level, index_size, and other index-related metrics.

```sql
test=> SELECT * FROM pgstatindex('pg_cast_oid_index');
-[ RECORD 1 ]------+------
version            | 2
tree_level         | 0
index_size         | 16384
root_block_no      | 1
internal_pages     | 0
leaf_pages         | 1
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 54.27
leaf_fragmentation | 0
```

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

### PL/pgSQL example using GET STACKED DIAGNOSTICS to capture error details

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

Example of using GET STACKED DIAGNOSTICS inside an EXCEPTION WHEN OTHERS handler to capture the error message, detail, and hint into text variables. The ellipsis (...) represents processing that might cause an exception.

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

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

### Prepare and Execute with SQL Descriptors

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

Example showing how to allocate a descriptor, prepare a statement with placeholders, and execute it using input and output descriptors. The statement uses '?' placeholders for parameters.

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

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

### Short Version: Complete Installation Commands

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

A quick-start sequence of commands to configure, build, install, and initialize a PostgreSQL cluster using Meson. Run as root for installation steps, then as the postgres user for database initialization and access.

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

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

### pg_get_wal_block_info example

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

Shows how to call pg_get_wal_block_info with start and end LSNs to get one row per block reference per WAL record. The output includes block-level details like block_id, relfilenode, and block_data. Rows are guaranteed to have a unique combination of start_lsn and block_id. Records with no block references (e.g., COMMIT) return no rows.

```sql
postgres=# SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');
-[ RECORD 1 ]-----+-----------------------------------
start_lsn         | 0/1230278
end_lsn           | 0/12302B8
prev_lsn          | 0/122FD40
block_id          | 0
reltablespace     | 1663
reldatabase       | 1
relfilenode       | 2658
relforknumber     | 0
relblocknumber    | 11
xid               | 341
resource_manager  | Btree
record_type       | INSERT_LEAF
record_length     | 64
main_data_length  | 2
block_data_length | 16
block_fpi_length  | 0
block_fpi_info    |
description       | off: 46
block_data        | \x00002a00070010402630000070696400
block_fpi_data    |
```

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

### make install-world - Install world build

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

Installs everything built with 'make world', including documentation.

```shell
make install-world
```

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

### pgstattuple example query

Source: https://www.postgresql.org/docs/current/pgstattuple.html

Example of using pgstattuple to get tuple-level statistics for a relation. The output shows columns like table_len, tuple_count, dead_tuple_count, and free_space.

```sql
test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc');
-[ RECORD 1 ]------+-------
table_len          | 458752
tuple_count        | 1470
tuple_len          | 438896
tuple_percent      | 95.67
dead_tuple_count   | 11
dead_tuple_len     | 3157
dead_tuple_percent | 0.69
free_space         | 8932
free_percent       | 1.95
```

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

### intarray example: create table, index, and queries

Source: https://www.postgresql.org/docs/current/intarray.html

Shows how to create a table with an integer array column, create a GiST index with gist__intbig_ops and siglen=32, and run queries using the &&, @>, and @@ operators.

```sql
-- a message can be in one or more “sections”
CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);

-- create specialized index with signature length of 32 bytes
CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32));

-- select messages in section 1 OR 2 - OVERLAP operator
SELECT message.mid FROM message WHERE message.sections && '{1,2}';

-- select messages in sections 1 AND 2 - CONTAINS operator
SELECT message.mid FROM message WHERE message.sections @> '{1,2}';

-- the same, using QUERY operator
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;
```

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

### Install extension with non-postgres prefix

Source: https://www.postgresql.org/docs/current/extend-pgxs.html

When the prefix does not contain 'postgres' or 'pgsql', 'postgresql' is appended to the directory names. This example installs control and SQL files into /usr/local/extras/share/postgresql/extension and shared modules into /usr/local/extras/lib/postgresql.

```makefile
make install prefix=/usr/local/extras
```

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

### Example: connect, open, and close a cursor

Source: https://www.postgresql.org/docs/current/contrib-dblink-close.html

Shows a complete workflow: connect to a database, open a cursor, then close it. Each command returns OK, demonstrating successful execution.

```sql
SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
 dblink_open
-------------
 OK
(1 row)

SELECT dblink_close('foo');
 dblink_close
--------------
 OK
(1 row)
```

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

### Examples of current date/time functions

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

Shows example outputs for CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(2), and LOCALTIMESTAMP. These functions return the start time of the current transaction and do not change during the transaction.

```sql
SELECT CURRENT_TIME;
Result: 14:39:53.662522-05
SELECT CURRENT_DATE;
Result: 2019-12-23
SELECT CURRENT_TIMESTAMP;
Result: 2019-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
Result: 2019-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
Result: 2019-12-23 14:39:53.662522
```

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

### Example Usage: Create table, GIN index, and query

Source: https://www.postgresql.org/docs/current/btree-gin.html

Shows how to create a table with an int4 column, create a GIN index on it, and run a query with a less-than condition. This example is from the 'Example Usage' section.

```sql
CREATE TABLE test (a int4);
-- create index
CREATE INDEX testidx ON test USING GIN (a);
-- query
SELECT * FROM test WHERE a < 10;
```

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

### Create composite type and table for ECPG examples

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

Defines the composite type comp_t and table t4 used in the following ECPG examples. This setup is required before running the retrieval programs.

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

```

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

### OPEN Examples

Source: https://www.postgresql.org/docs/current/ecpg-sql-open.html

Illustrates opening cursors with different binding methods: no values, direct values, SQL descriptor, and host variable for cursor name.

```sql
EXEC SQL OPEN a;
EXEC SQL OPEN d USING 1, 'test';
EXEC SQL OPEN c1 USING SQL DESCRIPTOR mydesc;
EXEC SQL OPEN :curname1;
```

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

### SET search_path and CREATE EXTENSION hstore

Source: https://www.postgresql.org/docs/current/sql-createextension.html

Alternative way to install hstore into the 'addons' schema by first setting the search_path to 'addons' and then creating the extension.

```sql
SET search_path = addons;
CREATE EXTENSION hstore;
```

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

### Example listing file output

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

Shows the format of the listing file: a header with archive metadata and lines for each 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
```

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

### Example: create table and query dblink_get_pkey

Source: https://www.postgresql.org/docs/current/contrib-dblink-get-pkey.html

Creates a table with a composite primary key and then queries dblink_get_pkey to show the positions and column names. The output shows three rows for the three primary key fields.

```sql
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
        3 | f3
(3 rows)
```

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

### EXPLAIN SELECT * FROM tenk1;

Source: https://www.postgresql.org/docs/current/using-explain.html

Basic EXPLAIN example showing a sequential scan on the tenk1 table. The output includes the query plan with cost, rows, and width estimates.

```sql
EXPLAIN SELECT * FROM tenk1;
```

```text
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)
```

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

### regexp_split_to_table and regexp_split_to_array examples

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

Examples of the regexp_split_to_table and regexp_split_to_array functions. The split functions ignore zero-length matches at the start or end of the string or immediately after a previous match, contrary to strict regexp matching.

```sql
SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
  foo
-------
 the
 quick
 brown
 fox
 jumps
 over
 the
 lazy
 dog
(9 rows)
```

```sql
SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
              regexp_split_to_array
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 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)
```

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

### make install-world-bin - Install world build without docs

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

Installs the world build when documentation was not built.

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

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

### PL/Tcl function returning composite using array get

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

Tip example showing how to build the 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 and EXPLAIN ANALYZE EXECUTE

Source: https://www.postgresql.org/docs/current/sql-explain.html

Shows how to use EXPLAIN EXECUTE to display the execution plan for a prepared query. The example prepares a query with parameters and then runs EXPLAIN ANALYZE EXECUTE, showing actual execution times and buffer usage.

```sql
PREPARE query(int, int) AS SELECT sum(bar) FROM test
    WHERE id > $1 AND id < $2
    GROUP BY foo;

EXPLAIN ANALYZE EXECUTE query(100, 200);

                                                       QUERY PLAN
-------------------------------------------------------------------​------------------------------------------------------
 HashAggregate  (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10.00 loops=1)
   Group Key: foo
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=4
   ->  Index Scan using test_pkey on test  (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99.00 loops=1)
         Index Cond: ((id > 100) AND (id < 200))
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.244 ms
 Execution Time: 0.073 ms
(10 rows)
```

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

### Typedef name conflicting with SQL keyword

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

Shows that a typedef name cannot be used as an SQL keyword later. This example defines start as a typedef, causing ECPG to report a syntax error for START TRANSACTION.

```ecpg
EXEC SQL BEGIN DECLARE SECTION;
    typedef int start;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL START TRANSACTION;
```

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

### Install documentation tools on Debian with apt-get

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

Use apt-get to install the full set of documentation tools: docbook-xml, docbook-xsl, libxml2-utils, xsltproc, and fop.

```bash
apt-get install docbook-xml docbook-xsl libxml2-utils xsltproc fop
```

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

### Example SELECT using connectby

Source: https://www.postgresql.org/docs/current/tablefunc.html

Example query that calls connectby with parameters for the source table, key fields, ordering, start value, max depth, and branch delimiter. The output columns are defined in the FROM clause.

```sql
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
    AS t(keyid text, parent_keyid text, level int, branch text, pos int);
```

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

### meson setup build

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

Creates and configures the build directory. The builddir is mandatory; srcdir is deduced if not provided.

```shell
meson setup build
```

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

### Pass extra options to initdb during test setup

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

Use PG_TEST_INITDB_EXTRA_OPTS to pass options to initdb commands run internally during test setup. Example enables checksums, sets WAL segment size, and work_mem.

```shell
make check PG_TEST_INITDB_EXTRA_OPTS='-k --wal-segsize=4 -c work_mem=50MB'
```

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

### Example output of transaction count DTrace script

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

Example output from the transaction count DTrace script, showing counts for Start, Commit, and Total time in nanoseconds. The output is triggered by pressing Ctrl+C after running the script.

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

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

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

### Example program managing multiple database connections

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

Connects to three databases (testdb1, testdb2, testdb3) and demonstrates the default current connection, using AT to run a query on a specific connection, and SET CONNECTION to switch. Output shows the expected database names.

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

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

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

### Translator comment example

Source: https://www.postgresql.org/docs/current/nls-programmer.html

Precede a string with a comment starting with 'translator' to communicate with translators. These comments are copied to message catalog files.

```c
/* translator: This message is not what it seems to be. */
```

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

### jsonb_path_query with filter expression

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

Example of using a filter expression in jsonb_path_query to select start times of segments with high heart rate.

```APIDOC
## jsonb_path_query with filter expression

### Description
Selects start times of segments where heart rate exceeds 130.

### Function
`jsonb_path_query(target jsonb, path jsonpath)`

### Parameters
- **target** (jsonb) - The JSON document to query.
- **path** (jsonpath) - The path expression with filter.

### Example
```sql
select jsonb_path_query(:'json', '$.track.segments[*] ? (@.HR > 130)."start time"');
```

### Response
Returns the matching start time as a jsonb value.

### Response Example
```
"2018-10-14 10:39:21"
```
```

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

### \connect examples: various connection syntaxes

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

Examples of the \connect meta-command using positional syntax, conninfo strings, and a URI. The -reuse-previous option controls parameter reuse. If a connection fails in interactive mode, the previous connection is kept; in scripts, it is closed and an error is reported.

```sql
=> \c mydb myuser host.dom 6432
```

```sql
=> \c service=foo
```

```sql
=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
```

```sql
=> \c -reuse-previous=on sslmode=require    -- changes only sslmode
```

```sql
=> \c postgresql://tom@localhost/mydb?application_name=myapp
```

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

### Install documentation tools on FreeBSD with pkg

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

Use pkg to install the required packages: docbook-xml, docbook-xsl, libxslt, and fop. When building from the doc directory, use gmake instead of make.

```bash
pkg install docbook-xml docbook-xsl libxslt fop
```

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

### Join pg_locks with pg_prepared_xacts

Source: https://www.postgresql.org/docs/current/view-pg-locks.html

Example query to join pg_locks with pg_prepared_xacts on the virtualtransaction column to get more information on prepared transactions that hold locks.

```sql
SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
    ON pl.virtualtransaction = '-1/' || ppx.transaction;
```

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

### Join pg_locks with pg_stat_activity

Source: https://www.postgresql.org/docs/current/view-pg-locks.html

Example query to join pg_locks with pg_stat_activity on the pid column to get more information on the session holding or awaiting each lock.

```sql
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;
```

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

### Example postgresql.conf content

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

Shows a sample configuration file with comments, a boolean parameter, a quoted string, a quoted search_path, and a size parameter. Demonstrates the syntax for setting parameters in postgresql.conf.

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

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

### Insert data into unit, shoe_data, and shoelace_data tables

Source: https://www.postgresql.org/docs/current/rules-views.html

Inserts sample data for units, shoes, and shoelaces. This is the setup data for the view examples.

```sql
INSERT INTO unit VALUES ('cm', 1.0);
INSERT INTO unit VALUES ('m', 100.0);
INSERT INTO unit VALUES ('inch', 2.54);

INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');

INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
```

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

### Example of role membership with INHERIT and SET options

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

Shows how to create roles and grant memberships with different INHERIT and SET options. This example demonstrates how privileges are inherited and how SET ROLE can be used to switch roles.

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

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

### Install extension with CREATE EXTENSION

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

Users typically install the extension that contains the language creation script by running CREATE EXTENSION plsample.

```sql
CREATE EXTENSION plsample;
```

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

### Prepared query plan lifecycle example

Source: https://www.postgresql.org/docs/current/plperl-builtins.html

Complete example of using a prepared plan across multiple functions: init creates the plan, add_time executes it, and done frees it. The output shows the results of add_time calls.

```perl
CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$
 $_SHARED{my_plan} = spi_prepare('SELECT (now() + $1)::date AS now',
 'INTERVAL');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
 return spi_exec_prepared(
 $_SHARED{my_plan},
 $_[0]
 )->{rows}->[0]->{now};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$
 spi_freeplan( $_SHARED{my_plan});
 undef $_SHARED{my_plan};
$$ LANGUAGE plperl;

SELECT init();
SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
SELECT done();

```

```text
 add_time | add_time | add_time
------------+------------+------------
 2005-12-10 | 2005-12-11 | 2005-12-12

```

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

### Build and install sepgsql-regtest policy package

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

Commands to build the sepgsql-regtest policy package from the source file sepgsql-regtest.te using a Makefile supplied by SELinux, then install it with semodule and verify it is listed. The path to the Makefile is an example; it is usually supplied by the selinux-policy-devel or selinux-policy RPM.

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

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

### pg_backup_start

Source: https://www.postgresql.org/docs/current/functions-admin.html

Prepares the server to begin an on-line backup. The only required parameter is an arbitrary user-defined label for the backup. If the optional second parameter is given as true, it specifies executing pg_backup_start as quickly as possible.

```APIDOC
## pg_backup_start(label text [, fast boolean ]) → pg_lsn

### Description
Prepares the server to begin an on-line backup. The only required parameter is an arbitrary user-defined label for the backup. (Typically this would be the name under which the backup dump file will be stored.) If the optional second parameter is given as `true`, it specifies executing `pg_backup_start` as quickly as possible. This forces an immediate checkpoint which will cause a spike in I/O operations, slowing any concurrently executing queries.

### Function Signature
`pg_backup_start(label text [, fast boolean ]) → pg_lsn`

### Parameters
- **label** (text) - Required - An arbitrary user-defined label for the backup.
- **fast** (boolean) - Optional - If `true`, executes `pg_backup_start` as quickly as possible, forcing an immediate checkpoint.

### Return Value
- **pg_lsn** - The write-ahead log location at the start of the backup.

### Permissions
Restricted to superusers by default, but other users can be granted EXECUTE to run the function.

### Usage Example
```sql
SELECT pg_backup_start('my_backup');
SELECT pg_backup_start('my_backup', true);
```
```

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

### heap_force_kill example: forcibly remove inaccessible tuple

Source: https://www.postgresql.org/docs/current/pgsurgery.html

Shows how to use heap_force_kill to remove a tuple that causes an error when accessed. The example first attempts to select the tuple, gets an error, then calls heap_force_kill with the tuple's tid, and finally verifies the tuple is gone.

```sql
test=> select * from t1 where ctid = '(0, 1)';
ERROR:  could not access status of transaction 4007513275
DETAIL:  Could not open file "pg_xact/0EED": No such file or directory.

test=# select heap_force_kill('t1'::regclass, ARRAY['(0, 1)']::tid[]);
 heap_force_kill
-----------------

(1 row)

test=# select * from t1 where ctid = '(0, 1)';
(0 rows)
```

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

### Create and initialize data directory

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

Shows the recommended process: create the parent directory, change ownership to the postgres user, switch to that user, and run initdb. initdb will refuse to run if the data directory exists and already contains files.

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

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

### Create database demo with custom server and template

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

Shows the createdb command and the underlying SQL command to create the database 'demo' on host 'eden', port 5000, using the 'template0' template database.

```shell
$ createdb -p 5000 -h eden -T template0 -e demo
```

```sql
CREATE DATABASE demo TEMPLATE template0;
```

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

### Error: connection to server on socket failed

Source: https://www.postgresql.org/docs/current/tutorial-createdb.html

This error means the server was not started or is not listening where createdb expects to contact it. Check installation instructions or consult the administrator.

```shell
createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
```

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

### OVERLAPS operator examples

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

Shows how the OVERLAPS operator works with date pairs and intervals, including the half-open interval behavior and the case where start equals end.

```sql
SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: true
```

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

### Example 1: Create subscription with connect=false

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

Creates a subscription sub1 with connect=false. The output shows a warning and hint that the subscription is not connected and requires manual slot creation and activation.

```sql
/* sub # */ CREATE SUBSCRIPTION sub1
/* sub - */ CONNECTION 'host=localhost dbname=test_pub'
/* sub - */ PUBLICATION pub1
/* sub - */ WITH (connect=false);
WARNING:  subscription was created, but is not connected
HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
```

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

### Start upgraded publisher on node1

Source: https://www.postgresql.org/docs/current/logical-replication-upgrade.html

Starts the upgraded PostgreSQL server on node1 with a log file.

```shell
pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
```