### Install PostgreSQL from Source - Short Version

Source: https://www.postgresql.org/docs/14/install-short.html

This sequence of commands outlines the essential steps for a quick installation of PostgreSQL from source. It covers configuration, compilation, installation, user creation, data directory setup, and initial server startup.

```bash
./configure
make
su
make install
adduser postgres
mkdir /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
```

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

### Start New PostgreSQL Server

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

Start the newly installed PostgreSQL server. Use the special database user account for this operation.

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

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

### Example Role Setup with Inheritance

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

Demonstrates creating roles with INHERIT and NOINHERIT attributes, and granting memberships to illustrate privilege inheritance behavior.

```sql
CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;
```

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

### OpenBSD rc.local Autostart Script

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

Example snippet to add to `/etc/rc.local` on OpenBSD for automatically starting the PostgreSQL server on boot. It checks for executable binaries and uses `pg_ctl`.

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

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

### Full Procedure Example: SELECT current_database()

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

Demonstrates a complete procedure to execute a query, fetch results into a descriptor, and then use GET DESCRIPTOR to retrieve the column count, data length, and data. Includes connection, cursor management, and cleanup.

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

```

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

### Create Partitions for Multi-Column Range Partitioned Table

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

This is a partial example showing the start of creating partitions for a range-partitioned table with multiple columns in its partition key.

```sql
CREATE TABLE measurement_ym_older
    PARTITION OF measurement_year_month
```

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

### Build and Install All Contrib Modules

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

To build and install all modules from the contrib directory, navigate to the 'contrib' directory in a configured source tree and run 'make' followed by 'make install'.

```bash
**make**
**make install**
```

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

### Install New PostgreSQL Binaries (Source Install)

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

When installing from source, use the 'prefix' variable to specify a custom installation location for the new PostgreSQL binaries.

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

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

### Solaris init.d Script for PostgreSQL

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

An example line for a Solaris `/etc/init.d/postgresql` script to start the PostgreSQL server as the `postgres` user using `pg_ctl`.

```bash
su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
```

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

### Configure pg_prewarm in postgresql.conf

Source: https://www.postgresql.org/docs/14/pgprewarm.html

Example configuration for enabling pg_prewarm and setting its autoprewarm interval in postgresql.conf. These parameters must be set at server start.

```ini
# postgresql.conf
shered_preload_libraries = 'pg_prewarm'

pg_prewarm.autoprewarm = true
pg_prewarm.autoprewarm_interval = 300s


```

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

### Makefile for 'pair' Extension Installation

Source: https://www.postgresql.org/docs/14/extend-extensions.html

A sample Makefile using PGXS to install the 'pair' extension files. Run 'make install' after setting up.

```makefile
EXTENSION = pair
DATA = pair--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

```

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

### Start PostgreSQL Server

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

Use this command to start the PostgreSQL server and wait until it is accepting connections.

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

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

### Install PostgreSQL (Full)

Source: https://www.postgresql.org/docs/14/install-windows-full.html

Installs all PostgreSQL files into the specified destination directory using the standard layout.

```batch
**install c:\destination\directory**

```

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

### Install Documentation Tools on Debian

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

Installs the documentation toolset on Debian GNU/Linux using `apt-get`.

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

```

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

### pg_ctl start

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

Starts a PostgreSQL server.

```APIDOC
## pg_ctl start

### Description
Starts a PostgreSQL server.

### Synopsis
`pg_ctl` `start` [`-D` _`datadir`_] [`-l` _`filename`_] [`-W`] [`-t` _`seconds`_] [`-s`] [`-o` _`options`_] [`-p` _`path`_] [`-c`]

### Parameters
#### Path Parameters
- **-D** (_datadir_) - Specifies the data directory.
- **-l** (_filename_) - Specifies a file to which the server log will be written.
- **-W** - Prompts for the server's password before starting.
- **-t** (_seconds_) - Specifies the time in seconds to wait for the server to start.
- **-s** - Suppresses normal output.
- **-o** (_options_) - Specifies options to be passed to the postgres command.
- **-p** (_path_) - Specifies the path to the postgres executable.
- **-c** - Allows the server to be started even if it is already running.
```

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

### Install PostgreSQL (Client Only)

Source: https://www.postgresql.org/docs/14/install-windows-full.html

Installs only the client applications and interface libraries into the specified destination directory.

```batch
**install c:\destination\directory client**

```

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

### Benchmark Setup and Execution

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

Steps to set up and run the intarray benchmark suite, including creating a database, enabling the extension, loading test data, and executing the benchmark script.

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

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

### Example Usage of dmetaphone()

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

Demonstrates how to use the dmetaphone() function to get the primary Double Metaphone code for a given string. No special setup is required beyond having the function available.

```sql
test=# SELECT dmetaphone('gumbo');
 dmetaphone
------------
 KMP
(1 row)
```

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

### GET DESCRIPTOR - Column Item Example (DATA)

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

Example of using GET DESCRIPTOR to retrieve the data of the second column as a string.

```APIDOC
## Example
```
EXEC SQL GET DESCRIPTOR d VALUE 2 :d_data = DATA;
```
```

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

### Start psql and Load Tutorial Script

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

Connect to your 'mydb' database using psql in single-step mode and load the tutorial commands from 'basics.sql' using the '\i' command.

```bash
$ psql -s mydb

mydb=> \i basics.sql
```

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

### GET DESCRIPTOR - Column Item Example (RETURNED_OCTET_LENGTH)

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

Example of using GET DESCRIPTOR to retrieve the data length of the first column.

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

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

### GET DESCRIPTOR - Header Item Example (COUNT)

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

Example of using GET DESCRIPTOR to retrieve the total number of columns in a result set.

```APIDOC
## Example
```
EXEC SQL GET DESCRIPTOR d :d_count = COUNT;
```
```

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

### Build INSTALL File

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

Recreate the INSTALL plain text file, which corresponds to Chapter 17 of the documentation, by running this command.

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

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

### GET DESCRIPTOR - Full Procedure Example

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

A complete C example demonstrating the use of GET DESCRIPTOR to retrieve column count, data length, and data after executing a SELECT statement.

```APIDOC
## Example
```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;
}
```
```

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

### Compile Tutorial Files

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

Navigate to the tutorial directory in the PostgreSQL source distribution and run 'make' to compile C files and create necessary scripts for the tutorial.

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

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

### Basic libpq Connection and Query Example (C)

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

Demonstrates establishing a connection to a PostgreSQL database, executing SQL commands within a transaction, fetching data using a cursor, and properly closing resources. Use this for standard database interactions.

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

```

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

### GET DIAGNOSTICS ROW_COUNT Example

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

This example shows how to assign the number of rows processed by the most recent SQL command to an integer variable.

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

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

### Getting Array Size

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

Use the `.size()` method to get the number of elements in a JSON array. This example returns the total number of track segments.

```sql
$.track.segments.size()
```

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

### Initialize PostgreSQL with sepgsql

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

This example demonstrates initializing a new PostgreSQL data directory and configuring it to use the sepgsql module. It involves modifying postgresql.conf and running sepgsql.sql for each database.

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


```

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

### Basic CREATE EXTENSION Syntax

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

Use this syntax to install a new extension. Ensure the extension's supporting files are installed and accessible.

```sql
CREATE EXTENSION [ IF NOT EXISTS ] _extension_name_
    [ WITH ] [ SCHEMA _schema_name_ ]
             [ VERSION _version_ ]
             [ CASCADE ]
```

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

### Run PostgreSQL Regression Tests (Existing Installation)

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

Execute regression tests against an already installed and running PostgreSQL server. Ensure the data directory is initialized and the server is started.

```bash
make installcheck
```

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

### Main Function and Query Preparation

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

Sets up the main function, declares the SQL query, connects to the database, and prepares the statement.

```c
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=? )";
    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 PREPARE stmt1 FROM :query;
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

```

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

### Verify Backup with External Manifest

Source: https://www.postgresql.org/docs/14/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 external manifest path.

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

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

### Example DTrace Script Output

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

This is an example of the output you might see when running the DTrace script for transaction analysis. It shows counts for transaction starts, commits, and total commit time.

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

Start                                          71
Commit                                         70
Total time (ns)                        2312105013


```

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

### Get Current Transaction Timestamp with transaction_timestamp()

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

Returns the timestamp at the start of the current transaction. This is equivalent to now().

```sql
transaction_timestamp()
```

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

### Example: Setting up hstore type and plpythonu extension

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

Before creating a transform for hstore and plpythonu, ensure the type is created and the language extension is enabled.

```sql
CREATE TYPE hstore ...;

CREATE EXTENSION plpythonu;

```

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

### PL/pgSQL Variable Initialization Examples

Source: https://www.postgresql.org/docs/14/plpgsql-declarations.html

Examples demonstrating how to initialize variables with default values or assign values using := or =. Constants must be initialized.

```plpgsql
quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

```

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

### PL/pgSQL Exception Handling with GET STACKED DIAGNOSTICS

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

This example demonstrates how to capture exception details like message text, detail, and hint using `GET STACKED DIAGNOSTICS` within an `EXCEPTION` block.

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

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

### Linux/Unix archive_cleanup_command Example

Source: https://www.postgresql.org/docs/14/pgarchivecleanup.html

An example of configuring archive_cleanup_command on Linux/Unix systems, directing debug output to a log file. This setup is useful when the archive directory is accessed via NFS but files are local to the standby.

```bash
archive_cleanup_command = 'pg_archivecleanup -d /mnt/standby/archive %r 2>>cleanup.log'
```

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

### Install PostgreSQL Documentation

Source: https://www.postgresql.org/docs/14/install-procedure.html

Use this command to install the HTML and man page documentation for PostgreSQL. This is typically run after building the source code.

```bash
make install-docs
```

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

### Get Current Transaction Timestamp with now()

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

The now() function returns the timestamp at the start of the current transaction. This is equivalent to transaction_timestamp().

```sql
now()
```

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

### Get Current Timestamp

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

Retrieves the current date and time at the start of the current transaction. Can be used with precision.

```sql
localtimestamp
```

```sql
localtimestamp(2)
```

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

### Get Current Time

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

Retrieves the current time of day at the start of the current transaction. Can be used with precision.

```sql
localtime
```

```sql
localtime(0)
```

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

### Dynamic Domain Transition Example

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

Demonstrates switching client security labels using sepgsql_setcon. Note that transitions to less privileged domains are allowed, while transitions back to more privileged domains may be denied.

```sql
regression=# select sepgsql_getcon();
                    sepgsql_getcon
-------------------------------------------------------
 unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
(1 row)

regression=# SELECT sepgsql_setcon('unconfined_u:unconfined_r:unconfined_t:s0-s0:c1.c4');
 sepgsql_setcon
----------------
 t
(1 row)

regression=# SELECT sepgsql_setcon('unconfined_u:unconfined_r:unconfined_t:s0-s0:c1.c1023');
ERROR:  SELinux: security policy violation


```

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

### ECPG CONNECT Examples

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

Demonstrates various ways to specify connection parameters, including database name, host, port, user, and password, using both literal strings and host variables.

```c
EXEC SQL CONNECT TO "connectdb" AS main;

```

```c
EXEC SQL CONNECT TO "connectdb" AS second;

```

```c
EXEC SQL CONNECT TO "unix:postgresql://200.46.204.71/connectdb" AS main USER connectuser;

```

```c
EXEC SQL CONNECT TO "unix:postgresql://localhost/connectdb" AS main USER connectuser;

```

```c
EXEC SQL CONNECT TO 'connectdb' AS main;

```

```c
EXEC SQL CONNECT TO 'unix:postgresql://localhost/connectdb' AS main USER :user;

```

```c
EXEC SQL CONNECT TO :db AS :id;

```

```c
EXEC SQL CONNECT TO :db USER connectuser USING :pw;

```

```c
EXEC SQL CONNECT TO @localhost AS main USER connectdb;

```

```c
EXEC SQL CONNECT TO REGRESSDB1 as main;

```

```c
EXEC SQL CONNECT TO AS main USER connectdb;

```

```c
EXEC SQL CONNECT TO connectdb AS :id;

```

```c
EXEC SQL CONNECT TO connectdb AS main USER connectuser;

```

```c
EXEC SQL CONNECT TO connectdb AS main;

```

```c
EXEC SQL CONNECT TO connectdb@localhost AS main;

```

```c
EXEC SQL CONNECT TO tcp:postgresql://localhost/ USER connectdb;

```

```c
EXEC SQL CONNECT TO tcp:postgresql://localhost/connectdb USER connectuser IDENTIFIED BY connectpw;

```

```c
EXEC SQL CONNECT TO tcp:postgresql://localhost:20/connectdb USER connectuser IDENTIFIED BY connectpw;

```

```c
EXEC SQL CONNECT TO unix:postgresql://localhost/ AS main USER connectdb;

```

```c
EXEC SQL CONNECT TO unix:postgresql://localhost/connectdb AS main USER connectuser;

```

```c
EXEC SQL CONNECT TO unix:postgresql://localhost/connectdb USER connectuser IDENTIFIED BY "connectpw";

```

```c
EXEC SQL CONNECT TO unix:postgresql://localhost/connectdb USER connectuser USING "connectpw";

```

```c
EXEC SQL CONNECT TO unix:postgresql://localhost/connectdb?connect_timeout=14 USER connectuser;

```

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

### Get pg_checksums Version

Source: https://www.postgresql.org/docs/14/app-pgchecksums.html

Prints the version of the pg_checksums utility and exits. This is useful for verifying the installed version.

```bash
pg_checksums --version
```

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

### Install PostgreSQL World Build

Source: https://www.postgresql.org/docs/14/install-procedure.html

If you built the entire PostgreSQL system ('world'), use this command to install both the binaries and documentation.

```bash
make install-world
```

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

### Control Logical Decoding with pg_recvlogical (Create, Start, Stop)

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

Provides an example of using the `pg_recvlogical` utility to manage logical decoding over the streaming replication protocol. This includes creating a slot, starting to receive changes, and stopping.

```bash
$ pg_recvlogical -d postgres --slot=test --create-slot
```

```bash
$ pg_recvlogical -d postgres --slot=test --start -f -
```

```bash
**Control**+**Z**
```

```bash
$ psql -d postgres -c "INSERT INTO data(data) VALUES('4');"
```

```bash
$ fg
```

```bash
BEGIN 693
table public.data: INSERT: id[integer]:4 data[text]:'4'
COMMIT 693
```

```bash
**Control**+**C**
```

```bash
$ pg_recvlogical -d postgres --slot=test --drop-slot
```

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

### PL/pgSQL Function for Obtaining Execution Location

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

This example shows how to use `GET DIAGNOSTICS` with the `PG_CONTEXT` item to retrieve the call stack information within a PL/pgSQL function. This differs from `GET STACKED DIAGNOSTICS` which is used for error information.

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

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

### Get SSL Cipher Used

Source: https://www.postgresql.org/docs/14/sslinfo.html

Returns the name of the cipher suite employed for the current SSL connection. An example output is DHE-RSA-AES256-SHA.

```sql
SELECT ssl_cipher();
```

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

### pgbench Initialization Command

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

Use this command to initialize the database with benchmark tables. Be cautious as it destroys existing tables with the same names.

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

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

### Connectby with Branch and Order

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

This example demonstrates the connectby function with the branch output enabled. It shows a hierarchical tree structure starting from 'row2'.

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

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

### Build All Components Including Documentation and Contrib

Source: https://www.postgresql.org/docs/14/install-procedure.html

Build everything that can be built, including documentation (HTML and man pages) and additional modules (`contrib`).

```bash
make world
```

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

### Example postgresql.conf Configuration

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

This is an example of a postgresql.conf file, used for setting server configuration parameters. Parameters are set using key-value pairs.

```postgresql
data_directory = '/var/lib/postgresql/14/main'

listen_addresses = 'localhost'
port = 5432

max_connections = 100
shared_buffers = 128MB

log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 10MB

# These are only relevant if logging_collector is disabled:
# log_output = 'stderr'

# These are only relevant if logging_collector is enabled:
# stderr_log_file_counts = 7
# stderr_log_file_max_size = 10MB

# Example of a parameter that can be set to a string value:
# ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
# ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

# Example of a parameter that can be set to a numeric value:
# shared_buffers = 128MB

# Example of a parameter that can be set to a boolean value:
# enable_partition_pruning = on

# Example of a parameter that can be set to an enumerated value:
# default_transaction_isolation = 'read committed'

# Example of a parameter that can be set to a time value with unit:
# statement_timeout = '30s'

# Example of a parameter that can be set to a memory value with unit:
# work_mem = '4MB'

# Example of a parameter that can be set to a floating point value:
# effective_cache_size = 0.75

```

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

### Create a database with default settings

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

Use this command to create a new database named 'demo' using the default PostgreSQL server connection parameters.

```bash
$ createdb demo
```

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

### Basic Connection URI Examples

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

These examples demonstrate the general structure of connection URIs. The scheme can be 'postgresql://' or 'postgres://'. Various components like user, host, database, and parameters are optional.

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

```

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

### Get SSL Connection Version

Source: https://www.postgresql.org/docs/14/sslinfo.html

Retrieves the protocol version used for the current SSL connection. Examples include TLSv1.0, TLSv1.1, TLSv1.2, or TLSv1.3.

```sql
SELECT ssl_version();
```

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

### Get Current Timestamp with Time Zone

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

Retrieves the current date and time, including the time zone, at the start of the current transaction. Can be used with precision.

```sql
current_timestamp
```

```sql
current_timestamp(0)
```

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

### Sample Application Using SQLDA with ECPG

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

Demonstrates preparing a statement, declaring a cursor, creating and populating an SQLDA for input parameters, opening the cursor, fetching data row by row, and processing different data types. Ensure proper memory management for SQLDA structures.

```c
EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

    EXEC SQL PREPARE stmt1 FROM :query;
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

    /* Create an SQLDA structure for an input parameter */
    sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
    memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
    sqlda2->sqln = 2; /* a number of input variables */

    sqlda2->sqlvar[0].sqltype = ECPGt_char;
    sqlda2->sqlvar[0].sqldata = "postgres";
    sqlda2->sqlvar[0].sqllen  = 8;

    intval = 1;
    sqlda2->sqlvar[1].sqltype = ECPGt_int;
    sqlda2->sqlvar[1].sqldata = (char *) &intval;
    sqlda2->sqlvar[1].sqllen  = sizeof(intval);

    /* Open a cursor with input parameters. */
    EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;

    while (1)
    {
        sqlda_t *cur_sqlda;

        /* Assign descriptor to the cursor  */
        EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;

        for (cur_sqlda = sqlda1 ;
             cur_sqlda != NULL ;
             cur_sqlda = cur_sqlda->desc_next)
        {
            int i;
            char name_buf[1024];
            char var_buf[1024];

            /* Print every column in a row. */
            for (i=0 ; i<cur_sqlda->sqld ; i++)
            {
                sqlvar_t v = cur_sqlda->sqlvar[i];
                char *sqldata = v.sqldata;
                short sqllen  = v.sqllen;

                strncpy(name_buf, v.sqlname.data, v.sqlname.length);
                name_buf[v.sqlname.length] = '\0';

                switch (v.sqltype)
                {
                    case ECPGt_char:
                        memset(&var_buf, 0, sizeof(var_buf));
                        memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) );
                        break;

                    case ECPGt_int: /* integer */
                        memcpy(&intval, sqldata, sqllen);
                        snprintf(var_buf, sizeof(var_buf), "%d", intval);
                        break;

                    case ECPGt_long_long: /* bigint */
                        memcpy(&longlongval, sqldata, sqllen);
                        snprintf(var_buf, sizeof(var_buf), "%lld", longlongval);
                        break;

                    default:
                    {
                        int i;
                        memset(var_buf, 0, sizeof(var_buf));
                        for (i = 0; i < sqllen; i++)
                        {
                            char tmpbuf[16];
                            snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]);
                            strncat(var_buf, tmpbuf, sizeof(var_buf));
                        }
                    }
                        break;
                }

                printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);
            }

            printf("\n");
        }
    }

    EXEC SQL CLOSE cur1;
    EXEC SQL COMMIT;

    EXEC SQL DISCONNECT ALL;

    return 0;
}

```

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

### Copy Filtered Table Data to File

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

Use this to export a subset of table data, based on a query, to a specified file. This example selects countries starting with 'A'.

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

```

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

### Install Extension After Setting Search Path

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

Installs the 'hstore' extension by first setting the search path to the 'addons' schema. This method is an alternative to explicitly specifying the schema in the CREATE EXTENSION command.

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

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

### Get Changes from Logical Slot (SQL)

Source: https://www.postgresql.org/docs/14/test-decoding.html

Retrieve changes from a logical decoding slot using SQL. This example shows how to include transaction IDs in the output.

```sql
SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'include-xids', '0');
```

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

### Start PostgreSQL on a Specific Port

Source: https://www.postgresql.org/docs/14/app-postgres.html

Launch the postgres server on a specified port, for example, 1234. This is useful for running multiple PostgreSQL instances or for specific network configurations.

```bash
postgres -p 1234
```

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

### Get Streamed Changes from Logical Slot (SQL)

Source: https://www.postgresql.org/docs/14/test-decoding.html

Retrieve streamed changes from a logical decoding slot, including those from in-progress transactions. This example uses the 'stream-changes' option.

```sql
SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'stream-changes', '1');
```

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

### Show All Run-Time Parameters in PostgreSQL

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

Use SHOW ALL to display all configuration parameters, their current settings, and descriptions. This provides a comprehensive overview of the server's configuration.

```sql
SHOW ALL;
```

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

### pgbench Version and Help Options

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

Use '-V' to print the pgbench version and exit. Use '-?' or '--help' to display command-line argument help and exit.

```bash
pgbench -V
```

```bash
pgbench --version
```

```bash
pgbench -?
```

```bash
pgbench --help
```

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

### Get Current Statement Timestamp with statement_timestamp()

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

Retrieves the timestamp at the start of the current SQL statement. This value remains constant within a single statement's execution.

```sql
statement_timestamp()
```

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

### Install PostgreSQL World Binaries Only

Source: https://www.postgresql.org/docs/14/install-procedure.html

If you built the PostgreSQL 'world' without documentation, use this command to install only the binaries.

```bash
make install-world-bin
```