PostgreSQL

https://www.postgresql.org/docs/13/
Admin
PostgreSQL is a powerful, open-source object-relational database system known for reliability,...
Tokens:1,919,158
Snippets:16,968
Trust Score:10
Update:1 month ago
Show doc for...
Tokens:
Raw
### 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
```

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

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

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

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

```

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

### 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');

```

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

### Install Documentation

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

Installs HTML and man pages for PostgreSQL.

```bash
make install-docs
```

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

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

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

### 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"?
```

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

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

```

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

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

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

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

```

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

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

```

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

### 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**

```

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

### 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') );

```

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

### 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**

```

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

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

```

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

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

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

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

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

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

```

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

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

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

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

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

### 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.
=======================


```
Tokens:
Raw