### Start the new database server

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

Start the PostgreSQL server instance using the new installation binaries.

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

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

### Setup for file_fdw materialized view example

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

Setup commands to create a foreign table from a local file, a materialized view, and indexes for fast spell-checking.

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

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

### libpq binary I/O example header (testlibpq3.c)

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

Header and setup comments for the libpq example program testlibpq3.c, which tests out-of-line parameters and binary I/O. The comments include the SQL commands to populate the test database (provided in testlibpq3.sql) and the expected output for two tuples. The code shown is only the beginning of the file, up to the Windows include guard.

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

```

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

### Setup and Usage of connectby

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

This example demonstrates creating a table for hierarchical data, inserting rows, and performing various queries using connectby with and without branch display and ordering.

```sql
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);

INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);

-- with branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text);
 keyid | parent_keyid | level |       branch
-------+--------------+-------+---------------------
 row2  |              |     0 | row2
 row4  | row2         |     1 | row2~row4
 row6  | row4         |     2 | row2~row4~row6
 row8  | row6         |     3 | row2~row4~row6~row8
 row5  | row2         |     1 | row2~row5
 row9  | row5         |     2 | row2~row5~row9
(6 rows)

-- without branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int);
 keyid | parent_keyid | level
-------+--------------+-------
 row2  |              |     0
 row4  | row2         |     1
 row6  | row4         |     2
 row8  | row6         |     3
 row5  | row2         |     1
 row9  | row5         |     2
(6 rows)

-- with branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 keyid | parent_keyid | level |       branch        | pos
-------+--------------+-------+---------------------+-----
 row2  |              |     0 | row2                |   1
 row5  | row2         |     1 | row2~row5           |   2
 row9  | row5         |     2 | row2~row5~row9      |   3
 row4  | row2         |     1 | row2~row4           |   4
 row6  | row4         |     2 | row2~row4~row6      |   5
 row8  | row6         |     3 | row2~row4~row6~row8 |   6
(6 rows)

-- without branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int, pos int);
 keyid | parent_keyid | level | pos
-------+--------------+-------+-----
 row2  |              |     0 |   1
 row5  | row2         |     1 |   2
 row9  | row5         |     2 |   3
 row4  | row2         |     1 |   4
 row6  | row4         |     2 |   5
 row8  | row6         |     3 |   6
(6 rows)
```

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

### Install Documentation

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

Installs HTML and man pages for PostgreSQL.

```bash
make install-docs
```

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

### Build and install sepgsql-regtest policy package

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

Commands to build and install the sepgsql-regtest policy package. The Makefile path is an example; locate the appropriate one on your system. After installation, semodule -l should list sepgsql-regtest.

```bash
$ cd .../contrib/sepgsql
$ make -f /usr/share/selinux/devel/Makefile
$ sudo semodule -u sepgsql-regtest.pp
$ sudo semodule -l | grep sepgsql
sepgsql-regtest 1.07

```

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

### Start an Interactive psql Session

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

Example of initiating a connection to a database named testdb, showing the resulting prompt.

```bash
$ **psql testdb**
psql (13.23)
Type "help" for help.

testdb=>
```

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

### Example postgresql.conf contents

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

Shows a minimal example of the postgresql.conf file with comments, boolean, string, and size parameters. One parameter per line; the equal sign is optional, whitespace is insignificant except within quoted values, and hash marks start comments.

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

```

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

### Install World Binaries

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

Installs the PostgreSQL world binaries without documentation.

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

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

### Install World

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

Installs the entire PostgreSQL world, including documentation.

```bash
make install-world
```

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

### Start PostgreSQL server with pg_ctl start

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

Starts the server and waits until it is accepting connections. Requires the data directory to be specified via -D or PGDATA.

```shell
$ **pg_ctl start**
```

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

### Client-only Installation

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

Installs only the client applications and interface libraries.

```bash
make -C src/bin install
make -C src/include install
make -C src/interfaces install
make -C doc install
```

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

### GET DIAGNOSTICS example with ROW_COUNT

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

Example of using GET DIAGNOSTICS to assign the ROW_COUNT status item to a variable. ROW_COUNT is of type bigint and contains the number of rows processed by the most recent SQL command.

```sql
GET DIAGNOSTICS integer_var = ROW_COUNT;

```

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

### Build with make or make all

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

Starts the build process. Both commands are equivalent; use GNU make. The build takes a few minutes depending on hardware.

```bash
make
make all
```

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

### PL/Tcl trigger function example: trigfunc_modcount

Source: https://www.postgresql.org/docs/13/pltcl-trigger.html

A complete example of a PL/Tcl trigger function that tracks the number of updates on a row. It uses the trigger arguments to get the column name, and returns a modified row using 'array get NEW'. The example includes the function definition, table creation, and trigger creation.

```sql
CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$
    switch $TG_op {
        INSERT {
            set NEW($1) 0
        }
        UPDATE {
            set NEW($1) $OLD($1)
            incr NEW($1)
        }
        default {
            return OK
        }
    }
    return [array get NEW]
$$ LANGUAGE pltcl;

CREATE TABLE mytab (num integer, description text, modcnt integer);

CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
    FOR EACH ROW EXECUTE FUNCTION trigfunc_modcount('modcnt');

```

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

### Error: could not connect to server

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

This error means the server was not started or not started where createdb expected it. Check installation instructions or consult the administrator.

```shell
createdb: could not connect to database postgres: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
```

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

### SHOW ALL example

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

Example output of SHOW ALL;. The result is a table with all configuration parameters, their settings, and descriptions, truncated here with ellipses and showing 196 rows.

```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 a PostgreSQL server with \c

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

Examples of establishing connections using positional arguments, service names, connection strings, parameter reuse, and URI syntax.

```sql
=> \c mydb myuser host.dom 6432
=> \c service=foo
=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
=> \c -reuse-previous=on sslmode=require    -- changes only sslmode
=> \c postgresql://tom@localhost/mydb?application_name=myapp
```

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

### generate_series examples

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

Examples of the generate_series function with integer, numeric, and timestamp inputs. They show positive and negative steps, zero rows when start exceeds stop, and the date-plus-integer operator. The timestamp example uses a 10-hour interval.

```sql
SELECT * FROM generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
(3 rows)

```

```sql
SELECT * FROM generate_series(5,1,-2);
 generate_series
-----------------
               5
               3
               1
(3 rows)

```

```sql
SELECT * FROM generate_series(4,3);
 generate_series
-----------------
(0 rows)

```

```sql
SELECT generate_series(1.1, 4, 1.3);
 generate_series
-----------------
             1.1
             2.4
             3.7
(3 rows)

```

```sql
-- this example relies on the date-plus-integer operator:
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19
(3 rows)

```

```sql
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
                              '2008-03-04 12:00', '10 hours');
   generate_series
---------------------
 2008-03-01 00:00:00
 2008-03-01 10:00:00
 2008-03-01 20:00:00
 2008-03-02 06:00:00
 2008-03-02 16:00:00
 2008-03-03 02:00:00
 2008-03-03 12:00:00
 2008-03-03 22:00:00
 2008-03-04 08:00:00
(9 rows)

```

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

### Obtaining Call Stack with GET DIAGNOSTICS PG_CONTEXT

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

This example demonstrates defining two functions, outer_func and inner_func, where the latter uses GET DIAGNOSTICS to capture and display the call stack.

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

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

### Example: Create table, index, and run intarray queries

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

Shows how to create a table with an integer array column, create a GiST index using gist__intbig_ops with a signature length of 32 bytes, and run queries using the &&, @>, and @@ operators. The comments indicate the purpose of each statement.

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

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

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

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

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

```

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

### libpq Example Program 1: testlibpq.c

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

Complete C program demonstrating libpq usage: connect with PQconnectdb, set search_path, use a cursor (DECLARE/FETCH/CLOSE) inside a transaction, and print results. Includes error handling with exit_nicely and proper cleanup with PQclear and PQfinish.

```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, "Connection to database failed: %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 index statistics with pgstatindex

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

Example of using the pgstatindex function to retrieve information about a B-tree index.

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

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

### Build tutorial files with make

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

Change to the tutorial directory in the PostgreSQL source distribution and run make to create the scripts and compile the C files containing user-defined functions and types.

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

```

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

### Permission denied error on reserved port

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

Example of the error output when attempting to start the server on a reserved port.

```bash
$ **postgres -p 666**
LOG:  could not bind IPv4 address "127.0.0.1": Permission denied
HINT:  Is another postmaster already running on port 666? If not, wait a few seconds and retry.
FATAL:  could not create any TCP/IP sockets
```

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

### Short version installation commands

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

A sequence of commands to configure, build, install, and initialize a PostgreSQL database instance from source.

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

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

### Create one-to-many tables

Source: https://www.postgresql.org/docs/13/intagg.html

Example schema setup for a one-to-many relationship between two tables using a linking table.

```sql
CREATE TABLE left (id INT PRIMARY KEY, ...);
CREATE TABLE right (id INT PRIMARY KEY, ...);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);
```

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

### DROP SERVER Example

Source: https://www.postgresql.org/docs/13/sql-dropserver.html

An example showing how to drop a server named 'foo' if it exists.

```sql
DROP SERVER IF EXISTS foo;
```

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

### substr(bytes bytea, start integer [, count integer]) -> bytea

Source: https://www.postgresql.org/docs/13/functions-binarystring.html

Extracts the substring of bytes starting at the start'th byte, and extending for count bytes if that is specified. Example: substr('\x1234567890'::bytea, 3, 2) returns \x5678.

```APIDOC
## substr(bytes bytea, start integer [, count integer]) -> bytea

### Description
Extracts the substring of bytes starting at the start'th byte, and extending for count bytes if that is specified.

### Function Signature
substr(bytes bytea, start integer [, count integer]) -> bytea

### Parameters
- **bytes** (bytea) - Required - The binary string.
- **start** (integer) - Required - The starting byte position.
- **count** (integer) - Optional - The number of bytes to extract.

### Return Type
bytea

### Example
substr('\x1234567890'::bytea, 3, 2) -> \x5678
```

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

### Create database demo with default settings

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

Creates a database named demo using the default database server connection settings.

```bash
$ **createdb demo**
```

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

### Create composite type and table for ECPG examples

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

Defines a composite type comp_t with an integer and a varchar field, creates a table t4 with a column of that type, and inserts a sample row. This setup is used by the 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') );

```

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

### Address already in use error log

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

Example of the log output when the server fails to start because the port is already in use.

```text
LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
FATAL:  could not create any TCP/IP sockets
```

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

### Configure OpenBSD startup in /etc/rc.local

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

Add these lines to /etc/rc.local to ensure the PostgreSQL server starts automatically on OpenBSD systems.

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

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

### Get table statistics with pgstattuple

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

Example of using the pgstattuple function to retrieve physical storage statistics for a specific table.

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

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

### PostgreSQL pg_hba.conf Configuration Examples

Source: https://www.postgresql.org/docs/13/auth-pg-hba-conf.html

A collection of configuration examples for pg_hba.conf demonstrating different connection types, authentication methods, and access control rules.

```text
# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

# The same as the previous line, but using a separate netmask column
#
# TYPE  DATABASE        USER            IP-ADDRESS      IP-MASK             METHOD
host    all             all             127.0.0.1       255.255.255.255     trust

# The same over IPv6.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             ::1/128                 trust

# The same using a host name (would typically cover both IPv4 and IPv6).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             localhost               trust

# Allow any user from any host with IP address 192.168.93.x to connect
# to database "postgres" as the same user name that ident reports for
# the connection (typically the operating system user name).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.93.0/24         ident

# Allow any user from host 192.168.12.10 to connect to database
# "postgres" if the user's password is correctly supplied.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.12.10/32        scram-sha-256

# Allow any user from hosts in the example.com domain to connect to
# any database if the user's password is correctly supplied.
#
# Require SCRAM authentication for most users, but make an exception
# for user 'mike', who uses an older client that doesn't support SCRAM
# authentication.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             mike            .example.com            md5
host    all             all             .example.com            scram-sha-256

# In the absence of preceding "host" lines, these three lines will
# reject all connections from 192.168.54.1 (since that entry will be
# matched first), but allow GSSAPI-encrypted connections from anywhere else
# on the Internet.  The zero mask causes no bits of the host IP address to
# be considered, so it matches any host.  Unencrypted GSSAPI connections
# (which "fall through" to the third line since "hostgssenc" only matches
# encrypted GSSAPI connections) are allowed, but only from 192.168.12.10.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.54.1/32         reject
hostgssenc all          all             0.0.0.0/0               gss
host    all             all             192.168.12.10/32        gss

# Allow users from 192.168.x.x hosts to connect to any database, if
# they pass the ident check.  If, for example, ident says the user is
# "bryanh" and he requests to connect as PostgreSQL user "guest1", the
# connection is allowed if there is an entry in pg_ident.conf for map
# "omicron" that says "bryanh" is allowed to connect as "guest1".
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.0.0/16          ident map=omicron

# If these are the only three lines for local connections, they will
# allow local users to connect only to their own databases (databases
# with the same name as their database user name) except for administrators
# and members of role "support", who can connect to all databases.  The file
# $PGDATA/admins contains a list of names of administrators.  Passwords
# are required in all cases.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   sameuser        all                                     md5
local   all             @admins                                 md5
local   all             +support                                md5

# The last two lines above can be combined into a single line:
local   all             @admins,+support                        md5

# The database column can also use lists and file names:
local   db1,db2,@demodbs  all                                   md5
```

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

### Start psql and run basics.sql

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

Start psql in single step mode with the -s option and run the basics.sql file using the \i command. The -s option pauses before sending each statement to the server.

```shell
$ **psql -s mydb**

...

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

```

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

### Procedure with COMMIT/ROLLBACK in a loop

Source: https://www.postgresql.org/docs/13/plpgsql-transactions.html

Example procedure transaction_test1 that inserts values into test1 and commits on even iterations, rolls back on odd ones. Demonstrates that a new transaction is automatically started after each COMMIT or ROLLBACK, so no explicit START TRANSACTION is needed.

```sql
CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();
```

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

### EXPLAIN with YAML format

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

Shows the same query as the previous example but with YAML output formatting using the FORMAT YAML option. The output is a YAML structure describing the plan.

```sql
EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
          QUERY PLAN
-------------------------------
 - Plan:                      +
     Node Type: "Index Scan"  +
     Scan Direction: "Forward"+
     Index Name: "fi"         +
     Relation Name: "foo"     +
     Alias: "foo"             +
     Startup Cost: 0.00       +
     Total Cost: 5.98         +
     Plan Rows: 1             +
     Plan Width: 4            +
     Index Cond: "(i = 4)"    
(1 row)

```

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

### Run parallel regression tests against a temporary installation with make check

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

Run in the top-level directory after building but before installation. This method starts a temporary server and will not work if the build was done as root. The expected output is a summary line indicating all tests passed.

```shell
make check

```

```output

=======================
 All 193 tests passed.
=======================


```

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

### SET CONNECTION examples

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

Two examples of using SET CONNECTION to switch to different connections: con2 and con1.

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

```

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

### substring(string [FROM start] [FOR count]) -> text

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

Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count. Examples: substring('Thomas' from 2 for 3) returns 'hom', substring('Thomas' from 3) returns 'omas', substring('Thomas' for 2) returns 'Th'.

```APIDOC
## substring(string [FROM start] [FOR count]) -> text

### Description
Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count.

### Function
`substring` ( string text [ FROM start integer ] [ FOR count integer ] ) -> `text`

### Examples
`substring('Thomas' from 2 for 3)` -> `hom`
`substring('Thomas' from 3)` -> `omas`
`substring('Thomas' for 2)` -> `Th`
```

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

### PL/pgSQL Event Trigger Function

Source: https://www.postgresql.org/docs/13/plpgsql-trigger.html

This example shows how to define an event trigger function that raises a NOTICE message when a DDL command starts.

```sql
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
```

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

### Equivalent schema creation with separate commands

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

Equivalent to the previous example, but using separate CREATE commands after creating the schema.

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

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

### Create database demo with specific server and template settings

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

Creates a database named demo on host eden at port 5000 using the template0 template, showing both the command-line execution and the resulting SQL command.

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

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

### Retrieving Exception Information

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

An example of using GET STACKED DIAGNOSTICS within an exception 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 language extension with CREATE EXTENSION

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

Shows how users would install the extension that typically contains the language creation script. Assumes the extension 'plsample' exists and is available in the database.

```sql
CREATE EXTENSION plsample;
```

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

### Create data directory and run initdb

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

Shows the recommended process for creating and owning the data directory before running initdb. Requires root privileges for the mkdir and chown commands, then switches to the postgres user.

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

```

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

### overlay(string PLACING newsubstring FROM start [FOR count]) -> text

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

Replaces the substring of string that starts at the start'th character and extends for count characters with newsubstring. If count is omitted, it defaults to the length of newsubstring. Example: overlay('Txxxxas' placing 'hom' from 2 for 4) returns 'Thomas'.

```APIDOC
## overlay(string PLACING newsubstring FROM start [FOR count]) -> text

### Description
Replaces the substring of string that starts at the start'th character and extends for count characters with newsubstring. If count is omitted, it defaults to the length of newsubstring.

### Function
`overlay` ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) -> `text`

### Example
`overlay('Txxxxas' placing 'hom' from 2 for 4)` -> `Thomas`
```

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

### Run configure for default installation

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

Runs the configure script with default options to set up the source tree for your system. This is the first step of the installation procedure.

```bash
./configure
```

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

### Configure Linux startup in rc.local

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

Add this command to /etc/rc.d/rc.local or /etc/rc.local to start the server on Linux systems.

```bash
/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
```

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

### position(substring IN string) -> integer

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

Returns first starting index of the specified substring within string, or zero if it's not present. Example: position('om' in 'Thomas') returns 3.

```APIDOC
## position(substring IN string) -> integer

### Description
Returns first starting index of the specified substring within string, or zero if it's not present.

### Function
`position` ( substring text IN string text ) -> `integer`

### Example
`position('om' in 'Thomas')` -> `3`
```

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

### Dynamic Domain Transition Example with sepgsql_getcon and sepgsql_setcon

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

Shows how to query the current security label and attempt to switch to a new domain. The first setcon call succeeds because the target range is smaller, while the second fails with an SELinux security policy violation because it attempts to switch back to a larger range.

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

```

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

### Implement production-grade row security for a passwd table

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

A comprehensive example showing table creation, role setup, enabling row-level security, and defining specific policies for administrators and normal users.

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

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

### Install PostgreSQL to a destination directory

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

Install files to a specified directory using the standard layout.

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

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

### btrim(bytes bytea, bytesremoved bytea) -> bytea

Source: https://www.postgresql.org/docs/13/functions-binarystring.html

Removes the longest string containing only bytes appearing in bytesremoved from the start and end of bytes. Example: btrim('\x1234567890'::bytea, '\x9012'::bytea) returns \x345678.

```APIDOC
## btrim(bytes bytea, bytesremoved bytea) -> bytea

### Description
Removes the longest string containing only bytes appearing in bytesremoved from the start and end of bytes.

### Function Signature
btrim(bytes bytea, bytesremoved bytea) -> bytea

### Parameters
- **bytes** (bytea) - Required - The binary string to trim.
- **bytesremoved** (bytea) - Required - The bytes to remove from the start and end.

### Return Type
bytea

### Example
btrim('\x1234567890'::bytea, '\x9012'::bytea) -> \x345678
```

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

### SHOW geqo example

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

Example output of SHOW geqo;. The result is a table with the parameter name and its current value, here on.

```sql
SHOW geqo;
 geqo
------
 on
(1 row)
```

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

### Create a security_barrier view

Source: https://www.postgresql.org/docs/13/rules-privileges.html

Creates a view with the security_barrier attribute to prevent malicious functions from being passed values from rows until after the view's filtering has been applied. This example shows a view that filters phone numbers not starting with '412%'.

```sql
CREATE VIEW phone_number WITH (security_barrier) AS
    SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';

```

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

### Create GiST and GIN indexes

Source: https://www.postgresql.org/docs/13/hstore.html

Examples of creating GiST and GIN indexes for hstore columns.

```sql
CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);
```

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

### trim([LEADING | TRAILING | BOTH] [characters] FROM string) -> text

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

Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string. Example: trim(both 'xyz' from 'yxTomxx') returns 'Tom'.

```APIDOC
## trim([LEADING | TRAILING | BOTH] [characters] FROM string) -> text

### Description
Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string.

### Function
`trim` ( [ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) -> `text`

### Example
`trim(both 'xyz' from 'yxTomxx')` -> `Tom`
```

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

### OPEN Examples

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

Example usages of the OPEN command: opening a cursor by name, with values, with a descriptor, and with a host variable.

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

```