### Configure Meson with Specific Options

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

Examples of `meson setup` commands to customize the installation prefix, build type, and enable features like OpenSSL support.

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

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

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

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

### Initialize PostgreSQL Cluster with sepgsql

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

This example demonstrates how to initialize 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

```

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

### PostgreSQL Short Installation Steps

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

Provides a concise sequence of commands for configuring, building, installing, and initializing PostgreSQL from source.

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

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

### pg_ctl start

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

Starts a PostgreSQL server instance.

```APIDOC
## pg_ctl start

### Description
Starts a PostgreSQL server instance. This command initiates the PostgreSQL server process, making the database available for connections.

### Command
`pg_ctl start`

### Arguments
- **-D _datadir_** (string) - Optional - Specifies the directory where the database cluster is stored.
- **-l _filename_** (string) - Optional - Appends server log output to _filename_.
- **-W** (flag) - Optional - Wait for the server to start or stop.
- **-t _seconds_** (integer) - Optional - Maximum time in seconds to wait for server start or stop.
- **-s** (flag) - Optional - Suppress all output except errors.
- **-o _options_** (string) - Optional - Specifies options to be passed directly to the `postgres` command.
- **-p _path_** (string) - Optional - Specifies the path to the `postgres` executable.
- **-c** (flag) - Optional - Attempt to start the server even if it's already running.
```

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

### Install New PostgreSQL Server with Custom Prefix (Source Install)

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

Installs the new PostgreSQL server binaries to a specified custom location using the 'prefix' variable during a source build.

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

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

### Start Upgraded PostgreSQL Publisher Server

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

Command to start the newly upgraded PostgreSQL server instance on the publisher node.

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

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

### Start PostgreSQL Server

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

Use this command to start the PostgreSQL server, waiting for it to accept connections.

```bash
$ **pg_ctl start**
```

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

### Example of DESCRIBE with SQL Descriptor

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

Demonstrates how to use `DESCRIBE` with an SQL descriptor to get metadata about a prepared statement, including allocating and deallocating the descriptor and retrieving a value.

```SQL
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/current/install-make.html

Installs the HTML and man pages documentation for PostgreSQL. This command is used when only the documentation needs to be installed.

```shell
**make install-docs**
```

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

### Install PostgreSQL (World Build)

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

Installs PostgreSQL and its documentation when a 'world' build was previously performed. This command ensures all components from a comprehensive build are installed.

```shell
**make install-world**
```

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

### Start Upgraded PostgreSQL Subscriber Server

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

Command to start the newly upgraded PostgreSQL server instance on the subscriber node.

```bash
pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
```

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

### Start PostgreSQL Tutorial with psql

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

Start the psql client in single-step mode for the `mydb` database and execute commands from the `basics.sql` file.

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

...

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

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

### Retrieve a Range of Enum Values in PostgreSQL

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

Demonstrates `enum_range` with two arguments to get a subset of enum values. Examples show ranges between specific values, starting from the first, and ending at the last.

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

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

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

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

### PL/pgSQL: Example of GET DIAGNOSTICS for ROW_COUNT

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

This example demonstrates retrieving the number of rows processed by the most recent SQL command into an integer variable using `GET DIAGNOSTICS`.

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

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

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

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

This example demonstrates creating a database named 'demo' on a specific host and port, utilizing a custom template database. It also displays the underlying SQL command executed by the utility.

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

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

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

### Connect to PostgreSQL, Execute Queries, and Fetch Results (C)

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

This comprehensive C example demonstrates establishing a database connection, setting a secure search path, managing transactions with BEGIN/END, declaring and fetching from a cursor, and processing query results using libpq functions.

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


```

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

### Example Output of DTrace Transaction Count Script

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

This is an example of the output generated when executing the DTrace script for transaction analysis. It shows counts for transaction 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

```

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

### Example Output of GET DESCRIPTOR C Program

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

This shows the expected console output when executing the provided C program that uses `GET DESCRIPTOR` to retrieve database information.

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

```

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

### Valid libpq Connection URI Examples

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

These examples demonstrate various valid libpq connection URI syntaxes, showing how to specify hosts, ports, users, databases, and query parameters.

```text
postgresql://
```

```text
postgresql://localhost
```

```text
postgresql://localhost:5433
```

```text
postgresql://localhost/mydb
```

```text
postgresql://user@localhost
```

```text
postgresql://user:secret@localhost
```

```text
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
```

```text
postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp
```

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

### Start PostgreSQL Server as Specific User with pg_ctl

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

Executes the `pg_ctl` command as the `postgres` user to ensure proper permissions when starting the server. This example specifies both the data directory and a log file for server output.

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

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

### Create Tables for Logical Replication Examples (PostgreSQL)

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

Use these SQL commands to set up the initial tables on the publisher node for demonstrating logical replication with row filters.

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

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

### Get Substring Position (PostgreSQL)

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

Returns the starting index of a specified substring within a string, or zero if not found.

```SQL
position('om' in 'Thomas')
```

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

### Initialize Meson Build Directory

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

Create and configure the build directory for PostgreSQL using the basic `meson setup` command.

```shell
meson setup build
```

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

### Get Current Timestamp with Time Zone (Transaction Start Time) in SQL

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

Returns the current date and time, including the time zone, fixed at the start of the current transaction. An optional integer argument specifies precision.

```SQL
current_timestamp
```

```SQL
current_timestamp(0)
```

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

### PL/pgSQL Example: Retrieving Error Details with GET STACKED DIAGNOSTICS

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

This PL/pgSQL block demonstrates how to declare variables and use GET STACKED DIAGNOSTICS within an EXCEPTION WHEN OTHERS handler to capture the error message, detail, and hint.

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

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

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

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

Installs PostgreSQL binaries when a 'world' build was performed without documentation. Use this if documentation was explicitly excluded from the 'world' build.

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

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

### Complete C Example for GET DESCRIPTOR with SELECT

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

This comprehensive C program demonstrates connecting to a database, executing a `SELECT` query, and using `GET DESCRIPTOR` to retrieve column count, data length, and data for the result set.

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

```

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

### BEGIN Command Syntax in PostgreSQL

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

Illustrates the full syntax for the BEGIN command, including optional keywords and transaction modes for isolation, read/write, and deferrable settings.

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

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

### Get IP Address Family (IPv6 Example)

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

Returns an integer representing the IP address family (4 for IPv4, 6 for IPv6).

```SQL
family(inet '::1')
```

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

### Simulated View Implementation with CREATE TABLE and RULE

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

This example illustrates the underlying mechanism of a view by showing how it could be conceptually implemented using a `CREATE TABLE` statement combined with an `ON SELECT DO INSTEAD` rule, although this specific rule syntax is not directly supported for tables.

```sql
CREATE TABLE myview (_same column list as mytab_);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;
```

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

### Setup Type and Language for Transform Example

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

Before creating a transform, ensure the target data type and procedural language are properly defined and available in the database.

```SQL
CREATE TYPE hstore ...;

CREATE EXTENSION plpython3u;
```

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

### Create and Populate Table for Soundex Queries

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

SQL commands to create a sample table `s` and insert data, preparing for queries that demonstrate `fuzzystrmatch` functions.

```SQL
CREATE TABLE s (nm text);

INSERT INTO s VALUES ('john');
INSERT INTO s VALUES ('joan');
INSERT INTO s VALUES ('wobbly');
INSERT INTO s VALUES ('jack');
```

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

### Prepare Directory and Initialize PostgreSQL Cluster

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

This sequence of commands demonstrates how to create a new directory, assign ownership to the PostgreSQL user, switch to that user, and then initialize the database cluster.

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

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

### Get Current Transaction Timestamp with Time Zone (PostgreSQL)

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

Retrieves the current date and time at the start of the current transaction, including time zone information.

```sql
now()
```

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

### Get Current Local Timestamp in SQL

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

Returns the current local date and time, fixed at the start of the current transaction, without time zone information.

```SQL
localtimestamp
```

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

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

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

This example shows how to retrieve the returned octet length for a specific column (here, the first column) from the descriptor.

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

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

### Complete PostgreSQL Installation with Meson

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

Execute a full sequence of commands to configure, build, install, and initialize a PostgreSQL instance using Meson and Ninja.

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

```

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

### Get Current Transaction Timestamp with Time Zone (PostgreSQL)

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

Returns the current date and time at the start of the current transaction, including time zone. This function is equivalent to `now()`.

```sql
transaction_timestamp()
```

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

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

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

This example shows how to create a role that possesses the `CREATEDB` privilege (allowing it to create new databases) and the `CREATEROLE` privilege (allowing it to manage other roles).

```sql
CREATE ROLE admin WITH CREATEDB CREATEROLE;


```

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

### Build and Install All Contrib Modules

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

Use these commands in the `contrib` directory of a configured source tree to build and install all optional components.

```bash
make
make install
```

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

### Get Current Statement Timestamp with Time Zone (PostgreSQL)

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

Returns the current date and time at the start of the current SQL statement, including time zone information.

```sql
statement_timestamp()
```

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

### SQL SHOW ALL Parameters

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

Displays all configuration parameters along with 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)
```

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

### Connect to Database with Host and Name

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

This example demonstrates connecting to a database named 'mydb' on 'sql.mydomain.com' using a direct target specification.

```sql
EXEC SQL CONNECT TO mydb@sql.mydomain.com;
```

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

### Stream Logical Changes with pg_recvlogical

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

Use `pg_recvlogical` to create a replication slot, start streaming changes, and then drop the slot. This example demonstrates decoding a simple `INSERT` transaction.

```bash
$ pg_recvlogical -d postgres --slot=test --create-slot
$ pg_recvlogical -d postgres --slot=test --start -f -
**Control**+**Z**
$ psql -d postgres -c "INSERT INTO data(data) VALUES('4');"
$ fg
BEGIN 693
table public.data: INSERT: id[integer]:4 data[text]:'4'
COMMIT 693
**Control**+**C**
$ pg_recvlogical -d postgres --slot=test --drop-slot
```

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

### Configure PL/Perl Interpreter Initialization with plperl.on_init

Source: https://www.postgresql.org/docs/current/plperl-under-the-hood.html

Use these examples to specify Perl code that runs when a Perl interpreter is first initialized, allowing for module loading or custom setup.

```Perl
plperl.on_init = 'require "plperlinit.pl"'
```

```Perl
plperl.on_init = 'use lib "/my/app"; use MyApp::PgInit;'
```

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

### Verify a Base Backup with an External Manifest

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

This example shows how to create a backup, move its manifest to a separate secure location, and then use pg_verifybackup with the -m option to specify the manifest path.

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

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

### Using date_bin with Different Origins

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

These examples demonstrate how to use date_bin to bin a timestamp into 15-minute intervals, illustrating how different origin timestamps affect the resulting bin start time.

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

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

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

### Transaction Control in PL/Tcl Procedure

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

This example demonstrates how to use `commit` and `rollback` commands within a PL/Tcl procedure to manage transactions. A new transaction is automatically started after each commit or rollback operation.

```sql
CREATE PROCEDURE transaction_test1()
LANGUAGE pltcl
AS $$
for {set i 0} {$i < 10} {incr i} {
    spi_exec "INSERT INTO test1 (a) VALUES ($i)"
    if {$i % 2 == 0} {
        commit
    } else {
        rollback
    }
}
$$;

CALL transaction_test1();
```

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

### Example LISTEN/NOTIFY Sequence in psql

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

Demonstrates configuring and executing a listen/notify sequence from within a psql session, showing how a notification is received.

```sql
LISTEN virtual;
NOTIFY virtual;
Asynchronous notification "virtual" received from server process with PID 8448.
```

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

### Get WAL Record Information for a Specific LSN

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

Retrieves detailed information about a single WAL record at or after the specified LSN. If the LSN is not at the start of a record, information for the next valid record is returned.

```SQL
postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');
```

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

### Out-of-line Parameters and Binary I/O with libpq (C)

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

This program illustrates how to handle out-of-line parameters and binary I/O using libpq. It requires a pre-populated database with specific table and data for demonstration, as detailed in the accompanying SQL script.

```c
/*
 * src/test/examples/testlibpq3.c
 *
 *
 * testlibpq3.c
 *      Test out-of-line parameters and binary I/O.
 *
 * Before running this, populate a database with the following commands
 * (provided in src/test/examples/testlibpq3.sql):
 *
 * CREATE SCHEMA testlibpq3;
 * SET search_path = testlibpq3;
 * SET standard_conforming_strings = ON;
 * CREATE TABLE test1 (i int4, t text, b bytea);
 * INSERT INTO test1 values (1, 'joe''s place', '\000\001\002\003\004');
 * INSERT INTO test1 values (2, 'ho there', '\004\003\002\001\000');
 *
 * The expected output is:
 *
 * tuple 0: got
 *  i = (4 bytes) 1
 *  t = (11 bytes) 'joe's place'
 *  b = (5 bytes) \000\001\002\003\004
 *
 * tuple 0: got
 *  i = (4 bytes) 2
 *  t = (8 bytes) 'ho there'
 *  b = (5 bytes) \004\003\002\001\000
 */

#ifdef WIN32
#include <windows.h>
#endif

#include <stdio.h>
#include <stdlib.h>
#include <stdint.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

```

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

### Define Composite Type and Table in SQL

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

This SQL snippet defines a composite type `comp_t` and a table `t4` that uses it, then inserts sample data. This setup is used for subsequent ECPG examples.

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

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

### Modified PostgreSQL Dump Table of Contents for Selective Restore

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

An example of a 'db.list' file modified to selectively restore only specific items (10 and 6). Lines starting with a semicolon are commented out and will be ignored by pg_restore.

```plaintext
10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres
```

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

### Specify Type Names with String Literals in PostgreSQL

Source: https://www.postgresql.org/docs/current/typeconv-overview.html

Use this snippet to explicitly assign data types to string literals within a SELECT statement, guiding the parser to interpret constants correctly from the start.

```sql
SELECT text 'Origin' AS "label", point '(0,0)' AS "value";
```

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

### Initialize pgbench database tables

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

Use these commands to set up the necessary tables for pgbench's default TPC-B-like transaction test before running a benchmark.

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

```bash
pgbench -i [ _other-options_ ] _dbname_
```

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

### Connect Using C Variables for Target and Credentials

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

This example illustrates how to use C host variables to dynamically provide connection target, username, and password to the CONNECT statement, enhancing portability and flexibility.

```c
EXEC SQL BEGIN DECLARE SECTION;
const char *target = "mydb@sql.mydomain.com";
const char *user = "john";
const char *passwd = "secret";
EXEC SQL END DECLARE SECTION;
 ...
EXEC SQL CONNECT TO :target USER :user USING :passwd;
/* or EXEC SQL CONNECT TO :target USER :user/:passwd; */
```

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

### Declare PL/Perl Call Handler Function in PostgreSQL

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

This command registers the call handler function for the PL/Perl language, specifying its shared library location. This is a concrete example of the first step in manually installing PL/Perl.

```SQL
CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS
    '$libdir/plperl' LANGUAGE C;
```

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

### Comprehensive Row Security Example for a Passwd-like Table

Source: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

This extensive example demonstrates setting up a `passwd` table, creating roles, populating data, enabling row-level security, defining multiple policies for different user types (admin, normal users), and granting specific privileges.

```SQL
-- Simple passwd-file based example
CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  extra_info            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);

CREATE ROLE admin;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Populate the table
INSERT INTO passwd VALUES
  ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
  ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
  ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Be sure to enable row-level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
CREATE POLICY user_mod ON passwd FOR UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  );

-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;
```

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

### Configure psql Editor Line Number Argument

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

These examples show how to set the PSQL_EDITOR_LINENUMBER_ARG variable to pass a starting line number to an external editor, using either a plus sign or a '--line' option.

```shell
PSQL_EDITOR_LINENUMBER_ARG='+'
```

```shell
PSQL_EDITOR_LINENUMBER_ARG='--line '
```

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

### Retrieve Multiple B-Tree Page Statistics with bt_multi_page_stats

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

Use `bt_multi_page_stats` to get summary information for a range of B-tree index pages. Specify the index name, starting block number, and the count of pages to report.

```sql
test=# SELECT * FROM bt_multi_page_stats('pg_proc_oid_index', 5, 2);
```

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

### Initial Table Structure for `mytab` Example

Source: https://www.postgresql.org/docs/current/transaction-iso.html

Illustrates the initial data in the `mytab` table used to demonstrate concurrent serializable transaction behavior and potential serialization anomalies.

```text
 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

```

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

### Create Monthly Partitions for a Range-Partitioned Table (PostgreSQL)

Source: https://www.postgresql.org/docs/current/ddl-partitioning.html

Examples of creating individual monthly partitions for the `measurement` table, demonstrating `FOR VALUES FROM ... TO` clauses, and specifying tablespace or storage parameters.

```sql
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;
```

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

### systemd Service Unit File for PostgreSQL

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

An example systemd service unit file, typically placed at `/etc/systemd/system/postgresql.service`, for managing the PostgreSQL database server. It defines how the server starts, reloads, and integrates with system boot processes.

```ini
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity

[Install]
WantedBy=multi-user.target
```

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

### Extract Week Number in PostgreSQL

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

Use EXTRACT(WEEK ...) to get the ISO 8601 week number from a TIMESTAMP or INTERVAL value. ISO weeks start on Mondays, and the first week contains January 4th.

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

```SQL
SELECT EXTRACT(WEEK FROM INTERVAL '13 days 24 hours');
```

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

### Build HTML Documentation with Make

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

Use this command in the `doc/src/sgml` directory to generate the default HTML version of the documentation. Output appears in the `html` subdirectory.

```bash
doc/src/sgml$ **make html**
```