### PostgreSQL Installation: Short Version

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

Provides a concise sequence of commands for configuring, building, installing, and initializing a basic PostgreSQL setup.

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

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

### Complete Crosstab Example with Table Setup

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

A full example demonstrating table creation, data insertion, and crosstab usage with a specific query and output definition.

```sql
CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = "att2" or attribute = "att3"
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

-- Expected Output:
--  row_name | category_1 | category_2 | category_3
-- ----------+------------+------------+------------
--  test1    | val2       | val3       | 
--  test2    | val6       | val7       | 
--(2 rows)
```

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

### Install PostgreSQL 17 with Meson (Short Version)

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

Provides a complete sequence of commands to configure, build, install, and initialize PostgreSQL 17 using Meson and Ninja, including user setup and database creation.

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

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

### Initialize PostgreSQL Cluster with sepgsql

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

This example demonstrates how to set up a new PostgreSQL database cluster, configure `shared_preload_libraries` for `sepgsql`, and install its functions and security labels.

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

```

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

### OpenBSD Autostart Script for PostgreSQL

Source: https://www.postgresql.org/docs/17/server-start.html

An example script for `/etc/rc.local` on OpenBSD to start the PostgreSQL server at boot, ensuring `pg_ctl` is run as the `postgres` user.

```bash
if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then
    su -l postgres -c '/usr/local/pgsql/bin/pg_ctl start -s -l /var/postgresql/log -D /usr/local/pgsql/data'
    echo -n ' postgresql'
fi
```

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

### Example Role Setup for Inheritance and SET ROLE

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

This snippet sets up several roles and grants membership with varying INHERIT and SET options to illustrate privilege behavior.

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

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

### Build and Install All Contrib Modules

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

To build and install all optional components from the contrib directory, run 'make' followed by 'make install'.

```bash
make
make install
```

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

### Configure Meson with Specific Build Options

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

Examples for configuring the Meson build with different installation prefixes, enabling debug builds, or adding support for libraries like OpenSSL.

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

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

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

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

### Example of DESCRIBE with SQL Descriptor

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

Demonstrates how to use `DESCRIBE` to get metadata for a prepared statement and then retrieve a column name using `GET DESCRIPTOR`.

```ecpg
EXEC SQL ALLOCATE DESCRIPTOR mydesc;
EXEC SQL PREPARE stmt1 FROM :sql_stmt;
EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;
EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :charvar = NAME;
EXEC SQL DEALLOCATE DESCRIPTOR mydesc;
```

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

### Install PostgreSQL Documentation

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

Installs the HTML and man pages documentation for PostgreSQL.

```shell
make install-docs
```

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

### Install PostgreSQL (World Build)

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

Installs all components of PostgreSQL, including documentation, when the build was configured with the 'world' option.

```shell
make install-world
```

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

### Install a procedural language via extension

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

This command shows the typical way users install a procedural language that has been packaged as an extension.

```SQL
CREATE EXTENSION plsample;
```

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

### Prepare PostgreSQL Tutorial Files

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

Navigate to the PostgreSQL source tutorial directory and run 'make' to compile necessary files and create scripts for the SQL tutorial examples.

```bash
$ **cd _..._/src/tutorial**
$ **make**
```

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

### Example: Getting Row Count with GET DIAGNOSTICS in PL/pgSQL

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

This example demonstrates how to use `GET DIAGNOSTICS` to retrieve the number of rows processed by the most recent SQL command into an integer variable.

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

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

### JSONPath Starts With Substring in SQL

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

Use `starts with` to check if a string operand begins with a specified substring. This example filters names that start with 'John'.

```SQL
jsonb_path_query('["John Smith", "Mary Stone", "Bob Johnson"]', '$[*] ? (@ starts with "John")')
```

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

### Autostart PostgreSQL with su and pg_ctl (Bash)

Source: https://www.postgresql.org/docs/17/server-start.html

Demonstrates how to start the PostgreSQL server at boot time using `su` to execute `pg_ctl` as the `postgres` user, ensuring proper permissions.

```bash
su postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog'
```

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

### Disable rpath for relocatable installs

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

Use this option with `meson setup` to ensure the installation remains relocatable after being moved.

```bash
-Drpath=false
```

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

### Create a Database with Custom Host, Port, and Template

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

This example demonstrates creating a database named `demo` on a specific host and port, using `template0`. It also shows the underlying SQL command executed.

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

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

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

### Incorrect Error Message Example

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

An example of an error message that is too long and mixes implementation details with hints, which should be avoided according to the style guide.

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

```

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

### Benchmark Setup and Execution

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

Provides commands to set up a test database, create the intarray extension, populate it with test data, and run the benchmark suite for the intarray extension.

```bash
cd .../contrib/intarray/bench
createdb TEST
psql -c "CREATE EXTENSION intarray" TEST
./create_test.pl | psql TEST
./bench.pl
```

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

### Initialize Meson Build Directory

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

Use 'meson setup' to create and configure the build directory. Meson automatically detects the source directory if not specified.

```bash
**meson setup build**
```

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

### PL/pgSQL: Retrieving Current Call Stack with GET DIAGNOSTICS PG_CONTEXT

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

This example demonstrates how GET DIAGNOSTICS stack = PG_CONTEXT captures the current execution call stack, useful for understanding function nesting and flow.

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

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

### Complete ECPG C Procedure for GET DESCRIPTOR Example

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

This C program demonstrates a full workflow using `GET DESCRIPTOR` to retrieve column count, data length, and data value after executing a `SELECT` query.

```C
int
main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
    int  d_count;
    char d_data[1024];
    int  d_returned_octet_length;
EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO testdb AS con1 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
    EXEC SQL ALLOCATE DESCRIPTOR d;

    /* Declare, open a cursor, and assign a descriptor to the cursor  */
    EXEC SQL DECLARE cur CURSOR FOR SELECT current_database();
    EXEC SQL OPEN cur;
    EXEC SQL FETCH NEXT FROM cur INTO SQL DESCRIPTOR d;

    /* Get a number of total columns */
    EXEC SQL GET DESCRIPTOR d :d_count = COUNT;
    printf("d_count                 = %d\n", d_count);

    /* Get length of a returned column */
    EXEC SQL GET DESCRIPTOR d VALUE 1 :d_returned_octet_length = RETURNED_OCTET_LENGTH;
    printf("d_returned_octet_length = %d\n", d_returned_octet_length);

    /* Fetch the returned column as a string */
    EXEC SQL GET DESCRIPTOR d VALUE 1 :d_data = DATA;
    printf("d_data                  = %s\n", d_data);

    /* Closing */
    EXEC SQL CLOSE cur;
    EXEC SQL COMMIT;

    EXEC SQL DEALLOCATE DESCRIPTOR d;
    EXEC SQL DISCONNECT ALL;

    return 0;
}
```

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

### Verify Backup with External Manifest File

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

This example shows how to create a base backup, move its manifest file to a secure location, and then verify the backup using the --manifest-path option.

```bash
$ **pg_basebackup -h mydbserver -D /usr/local/pgsql/backup1234**
$ **mv /usr/local/pgsql/backup1234/backup_manifest /my/secure/location/backup_manifest.1234**
$ **pg_verifybackup -m /my/secure/location/backup_manifest.1234 /usr/local/pgsql/backup1234**
```

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

### Pseudo-code Example for C Set-Returning Function

Source: https://www.postgresql.org/docs/17/xfunc-c.html

This pseudo-code illustrates the basic structure of a C set-returning function (SRF), showing the first-call setup, per-call setup, and how to return items or signal completion using SRF_RETURN_NEXT and SRF_RETURN_DONE.

```C
Datum
my_set_returning_function(PG_FUNCTION_ARGS)
{
    FuncCallContext  *funcctx;
    Datum             result;
    _further declarations as needed_

    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext oldcontext;

        funcctx = SRF_FIRSTCALL_INIT();
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
        /* One-time setup code appears here: */
        _user code_
        _if returning composite_
            _build TupleDesc, and perhaps AttInMetadata_
        _endif returning composite_
        _user code_
        MemoryContextSwitchTo(oldcontext);
    }

    /* Each-time setup code appears here: */
    _user code_
    funcctx = SRF_PERCALL_SETUP();
    _user code_

    /* this is just one way we might test whether we are done: */
    if (funcctx->call_cntr < funcctx->max_calls)
    {
        /* Here we want to return another item: */
        _user code_
        _obtain result Datum_
        SRF_RETURN_NEXT(funcctx, result);
    }
    else
    {
        /* Here we are done returning items, so just report that fact. */
        /* (Resist the temptation to put cleanup code here.) */
        SRF_RETURN_DONE(funcctx);
    }
}

```

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

### Get Current Transaction Timestamp with now() in PostgreSQL

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

The now() function returns the current date and time at the start of the current transaction as a timestamp with time zone.

```sql
now()
```

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

### Install PostgreSQL Binaries (World Build without Docs)

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

Installs PostgreSQL binaries and other components, excluding documentation, for a 'world' build.

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

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

### PL/pgSQL: Example of GET STACKED DIAGNOSTICS in Exception Handler

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

This snippet illustrates capturing MESSAGE_TEXT, PG_EXCEPTION_DETAIL, and PG_EXCEPTION_HINT into variables when an exception occurs.

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

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

### Check if String Starts With Prefix in PostgreSQL

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

Returns true if a string begins with the specified prefix. For example, `starts_with('alphabet', 'alph')` returns `t`.

```sql
SELECT starts_with('alphabet', 'alph');
```

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

### Install Extension by Setting Search Path (SQL)

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

This method first sets the search_path to the desired schema, then installs the extension, placing its objects into that schema.

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

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

### Copy Query Results to File (SQL)

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

Export the results of a `SELECT` query directly to a file. This example copies countries whose names start with 'A'.

```sql
COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

```

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

### Get Current Transaction Timestamp with transaction_timestamp() in PostgreSQL

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

The transaction_timestamp() function returns the current date and time at the start of the current transaction as a timestamp with time zone.

```sql
transaction_timestamp()
```

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

### SQL: Create User Mapping for Bob on Server Foo

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

This example demonstrates how to create a user mapping for a specific user, 'bob', to a foreign server named 'foo', providing the user's credentials as options.

```sql
CREATE USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'secret');
```

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

### Get Current Statement Timestamp with statement_timestamp() in PostgreSQL

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

The statement_timestamp() function returns the current date and time at the start of the current statement as a timestamp with time zone.

```sql
statement_timestamp()
```

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

### Start psql and Execute SQL Basics Script

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

Launch the psql client in single-step mode for the 'mydb' database and then execute the commands from the 'basics.sql' file.

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

...

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

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

### Create Subscription with Multiple Publications

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

This example creates a subscription to a remote server, replicating tables from mypublication and insert_only, with replication starting immediately upon commit.

```sql
CREATE SUBSCRIPTION mysub
         CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
        PUBLICATION mypublication, insert_only;

```

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

### Create a Role with Database and Role Creation Privileges (SQL)

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

This example creates a role with the `CREATEDB` and `CREATEROLE` attributes, allowing it to create new databases and manage other roles.

```sql
CREATE ROLE admin WITH CREATEDB CREATEROLE;
```

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

### Basic libpq Usage for Database Connection and Query Execution in C

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

This example demonstrates connecting to a PostgreSQL database, executing SQL commands within a transaction, using a cursor to fetch results, and processing the returned data. It includes error handling and proper resource cleanup.

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

```

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

### Retrieve Array Length with array_length in PostgreSQL

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

The array_length function returns the number of elements in a specified array dimension. This example gets the length of the first dimension.

```sql
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_length
--------------
            2
(1 row)

```

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

### Get Local Timestamp with Optional Precision (PostgreSQL)

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

Retrieves the current local date and time at the start of the current transaction, optionally specifying fractional seconds precision.

```SQL
localtimestamp
```

```SQL
localtimestamp(2)
```

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

### Find Substring Position in PostgreSQL

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

Returns the starting index (1-based) of a substring within a string, or 0 if not found. For example, `strpos('high', 'ig')` returns `2`.

```sql
SELECT strpos('high', 'ig');
```

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

### Displaying All Configuration Parameters in SQL

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

Use `SHOW ALL` to retrieve a comprehensive list of all PostgreSQL run-time configuration parameters, their current settings, and descriptions.

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

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

### Example Output of DTrace Transaction Count Script

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

This shows typical output when executing the DTrace script to count transactions, including start, commit, and total time.

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

Start                                          71
Commit                                         70
Total time (ns)                        2312105013

```

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

### Create a Database with Default Settings

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

Use this command to create a new database named `demo` on the default PostgreSQL server without specifying additional parameters.

```shell
$ createdb demo
```

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

### pgbench Command Synopsis

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

These are the general command line syntaxes for using pgbench, covering both database initialization and running benchmark tests.

```shell
pgbench -i [_option_...] [_dbname_]
```

```shell
pgbench [_option_...] [_dbname_]
```

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

### Get Current Timestamp with Optional Precision (PostgreSQL)

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

Retrieves the current date and time at the start of the current transaction. An optional integer argument specifies fractional seconds precision.

```SQL
current_timestamp
```

```SQL
current_timestamp(0)
```

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

### Install Documentation Tools on Debian using apt-get

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

Use this command to install the full set of documentation tools available for Debian GNU/Linux.

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

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

### Create List Partition for a Table

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

This example shows how to create a list partition for a table, including a constraint on the partitioned column.

```sql
CREATE TABLE cities_ab
    PARTITION OF cities (
    CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b');
```

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

### Filter Rows Before Aggregation with WHERE and GROUP BY

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

Apply a WHERE clause to filter rows before grouping and aggregation, which is more efficient than filtering groups with HAVING for non-aggregate conditions. This example filters cities starting with 'S'.

```sql
SELECT city, count(*), max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'
    GROUP BY city;
```

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

### Examples of Setting Database Connections

Source: https://www.postgresql.org/docs/17/ecpg-sql-set-connection.html

These examples demonstrate how to use `SET CONNECTION` to switch to different database connections, 'con2' and 'con1', using both the `TO` keyword and the `=` operator.

```sql
EXEC SQL SET CONNECTION TO con2;
EXEC SQL SET CONNECTION = con1;
```

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

### Build HTML Documentation with Meson

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

Run this command from the `build` directory to generate the HTML version of the documentation.

```bash
build$ **ninja html**
```

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

### Retrieve Column Data Length with GET DESCRIPTOR (ECPG SQL)

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

This example retrieves the returned octet length of the first column from the descriptor area into a host variable.

```ECPG SQL
EXEC SQL GET DESCRIPTOR d VALUE 1 :d_returned_octet_length = RETURNED_OCTET_LENGTH;
```

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

### ECPG C Program Initial Setup and Connection

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

Illustrates the basic structure of an ECPG application, including necessary headers, global SQLDA declarations, error handling, and initial database connection.

```C
#include <stdlib.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>

EXEC SQL include sqlda.h;

sqlda_t *sqlda1; /* descriptor for output */
sqlda_t *sqlda2; /* descriptor for input */

EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL WHENEVER SQLERROR STOP;

int
main(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
    char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";

    int intval;
    unsigned long long int longlongval;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO uptimedb AS con1 USER uptime;
```

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

### Retrieve Array Upper Bound with array_upper in PostgreSQL

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

The array_upper function returns the upper bound of a specified array dimension. This example gets the upper bound of the first dimension.

```sql
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

```

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

### Basic LISTEN Command Syntax

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

Illustrates the fundamental syntax for the `LISTEN` command, specifying the channel name to subscribe to.

```sql
LISTEN _channel_
```

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

### Extracting ISO Week Number from Timestamp in PostgreSQL

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

Use EXTRACT with the WEEK field to get the ISO 8601 week number of the year for a TIMESTAMP. ISO weeks start on Mondays.

```sql
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
_Result: _7
```

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

### SQL Query and libpq Column Name/Index Examples

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

Demonstrates how column names are handled by PQfname and PQfnumber, especially regarding SQL identifier casing and quoting.

```SQL
SELECT 1 AS FOO, 2 AS "BAR";
```

```C
PQfname(res, 0)              _foo_
PQfname(res, 1)              _BAR_
PQfnumber(res, "FOO")        _0_
PQfnumber(res, "foo")        _0_
PQfnumber(res, "BAR")        _-1_
PQfnumber(res, "\"BAR\"")    _1_
```

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

### Binning Timestamp to 15-Minute Intervals with date_bin

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

The date_bin function bins a timestamp into intervals (stride) aligned with an origin. This example bins a timestamp to the nearest 15-minute interval starting from '2001-01-01'.

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

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

### Example of LISTEN and NOTIFY in psql

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

Demonstrates a simple sequence of `LISTEN` and `NOTIFY` commands executed in a psql session, showing how a notification is received.

```sql
LISTEN virtual;
NOTIFY virtual;
```

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

### Use WITH ORDINALITY with Set-Returning Functions in SQL

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

This example demonstrates appending an ordinality column to the output of a set-returning function, specifically `pg_ls_dir()`, showing how to get both the directory listing and a row number.

```sql
-- set returning function WITH ORDINALITY:
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
       ls        | n
-----------------+----
 pg_serial       |  1
 pg_twophase     |  2
 postmaster.opts |  3
 pg_notify       |  4
 postgresql.conf |  5
 pg_tblspc       |  6
 logfile         |  7
 base            |  8
 postmaster.pid  |  9
 pg_ident.conf   | 10
 global          | 11
 pg_xact         | 12
 pg_snapshots    | 13
 pg_multixact    | 14
 PG_VERSION      | 15
 pg_wal          | 16
 pg_hba.conf     | 17
 pg_stat_tmp     | 18
 pg_subtrans     | 19
(19 rows)
```

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

### BEGIN Command Syntax

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

This snippet illustrates the full syntax for the `BEGIN` command, including optional keywords like `WORK` and `TRANSACTION`, and various `_transaction_mode_` options for specifying isolation level, read/write mode, and deferrable status.

```sql
BEGIN [ WORK | TRANSACTION ] [ _transaction_mode_ [, ...] ]

where _transaction_mode_ is one of:

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE
```

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

### Retrieve Current Date and Time in PostgreSQL

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

Use SQL-standard functions like CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP to get the transaction's start time. CURRENT_TIMESTAMP can take a precision parameter.

```sql
SELECT CURRENT_TIME;
```

```sql
SELECT CURRENT_DATE;
```

```sql
SELECT CURRENT_TIMESTAMP;
```

```sql
SELECT CURRENT_TIMESTAMP(2);
```

```sql
SELECT LOCALTIMESTAMP;
```

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

### EXPLAIN ANALYZE with Sort and Hash Nodes

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

This example demonstrates `EXPLAIN ANALYZE` for a query with `ORDER BY`, showing additional statistics for Sort and Hash nodes, including sort method, memory usage, hash buckets, and batches.

```sql
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;

                                                                 QUERY PLAN
-------------------------------------------------------------------​-------------------------------------------------------------------​------
 Sort  (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 74kB
   ->  Hash Join  (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1)
         Hash Cond: (t2.unique2 = t1.unique2)
         ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1)
         ->  Hash  (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 35kB
               ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100 loops=1)
                     Recheck Cond: (unique1 < 100)
                     Heap Blocks: exact=90
                     ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning Time: 0.187 ms
 Execution Time: 3.036 ms
```

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

### Create Table with SQL Array Column

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

This SQL snippet demonstrates creating a table with an `integer[]` column and a sample query showing its content, used as a setup for subsequent ECPG array examples.

```sql
CREATE TABLE t3 (
    ii integer[]
);

testdb=> SELECT * FROM t3;
     ii
-------------
 {1,2,3,4,5}
(1 row)

```

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

### Example: Listing Text Search Templates in psql

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

This example demonstrates the output when executing the "\dFt" command in psql, showing the default text search templates.

```psql
=> \dFt
                           List of text search templates
   Schema   |   Name    |                        Description
------------+-----------+-----------------------------------------------------------
 pg_catalog | ispell    | ispell dictionary
 pg_catalog | simple    | simple dictionary: just lower case and check for stopword
 pg_catalog | snowball  | snowball stemmer
 pg_catalog | synonym   | synonym dictionary: replace word by its synonym
 pg_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution

```

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

### Extract Substring in PostgreSQL

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

Extracts a substring from a string, starting at a specified character and optionally for a given count of characters. For example, `substr('alphabet', 3)` returns `phabet`, and `substr('alphabet', 3, 2)` returns `ph`.

```sql
SELECT substr('alphabet', 3);
```

```sql
SELECT substr('alphabet', 3, 2);
```