PostgreSQL 14

https://www.postgresql.org/docs/14
Admin
PostgreSQL 14 is a powerful, open-source object-relational database system known for its...
Tokens:1,734,880
Snippets:16,550
Trust Score:10
Update:1 month ago
Show doc for...
Tokens:
Raw
### Start the new database server

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

Start the database server using the postgres binary from the new installation.

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

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

### libpq Binary I/O and Parameters Example (testlibpq3.c)

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

Demonstrates the use of out-of-line parameters and binary I/O in libpq. The example includes setup SQL commands and the initial C boilerplate for the program.

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

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

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

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

### Start a psql Session

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

Example of starting a psql session for a specific database, showing the initial prompt.

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

testdb=>
```

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

### Create computer and software tables

Source: https://www.postgresql.org/docs/14/rules-triggers.html

Initial schema setup for the computer and software tables used in the comparison examples.

```sql
CREATE TABLE computer (
    hostname        text,    -- indexed
    manufacturer    text     -- indexed
);

CREATE TABLE software (
    software        text,    -- indexed
    hostname        text     -- indexed
);
```

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

### Setup and Usage of connectby

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

This example demonstrates creating a tree table and querying it using connectby with various combinations of branch and orderby_fld parameters.

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

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

### Manual installation of PL/Perl

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

Example demonstrating the manual installation of the PL/Perl language, including the declaration of the call handler, inline handler, validator, and the final language definition.

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

```sql
CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS
    '$libdir/plperl' LANGUAGE C STRICT;

CREATE FUNCTION plperl_validator(oid) RETURNS void AS
    '$libdir/plperl' LANGUAGE C STRICT;
```

```sql
CREATE TRUSTED LANGUAGE plperl
    HANDLER plperl_call_handler
    INLINE plperl_inline_handler
    VALIDATOR plperl_validator;
```

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

### FETCH Examples

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

Examples of using FETCH with different directions and targets.

```sql
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;
```

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

### Start the server

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

Starts the server and waits until it is accepting connections.

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

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

### Setup for multivalued results example

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

Creates a test table and inserts XML documents to demonstrate how xpath_table handles multivalued results.

```sql
CREATE TABLE test (
    id int PRIMARY KEY,
    xml text
);

INSERT INTO test VALUES (1, '<doc num="C1">
<line num="L1"><a>1</a><b>2</b><c>3</c></line>
<line num="L2"><a>11</a><b>22</b><c>33</c></line>
</doc>');

INSERT INTO test VALUES (2, '<doc num="C2">
<line num="L1"><a>111</a><b>222</b><c>333</c></line>
<line num="L2"><a>111</a><b>222</b><c>333</c></line>
</doc>');
```

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

### Begin a transaction block

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

Example of starting a transaction block.

```sql
BEGIN;
```

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

### Configure role inheritance example

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

Setup roles with varying INHERIT attributes to demonstrate privilege inheritance.

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

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

### Install PostgreSQL world

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

Installs the entire build including documentation.

```bash
**make install-world**
```

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

### DROP SERVER Example

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

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

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

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

### MOVE Examples

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

Examples of using MOVE to reposition a cursor.

```sql
MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;
```

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

### RETURN command examples

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

Examples of using RETURN for scalar and composite types.

```plpgsql
-- functions returning a scalar type
RETURN 1 + 2;
RETURN scalar_var;

-- functions returning a composite type
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- must cast columns to correct types
```

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

### Install documentation tools on Debian

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

Use apt-get to install the necessary documentation toolchain.

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

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

### OPEN FOR example

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

Example of opening an unbound cursor with a SELECT query.

```plpgsql
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
```

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

### Install PostgreSQL documentation

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

Installs HTML and man pages for the PostgreSQL system.

```bash
**make install-docs**
```

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

### Install PostgreSQL world binaries

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

Installs the world build excluding documentation.

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

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

### IF-THEN-ELSE Examples

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

Examples demonstrating conditional logic with alternative execution paths.

```plpgsql
IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
```

```plpgsql
IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;
```

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

### PREPARE TRANSACTION Example

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

An example of preparing the current transaction with the identifier 'foobar'.

```sql
PREPARE TRANSACTION 'foobar';
```

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

### Install client-only components

Source: https://www.postgresql.org/docs/14/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**
```

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

### xmlforest Examples

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

Examples of creating an XML forest from constants and table columns.

```sql
SELECT xmlforest('abc' AS foo, 123 AS bar);

          xmlforest
------------------------------
 <foo>abc</foo><bar>123</bar>


SELECT xmlforest(table_name, column_name)
FROM information_schema.columns
WHERE table_schema = 'pg_catalog';

                                xmlforest
-----------------------------------------------------------------------
 <table_name>pg_authid</table_name><column_name>rolname</column_name>
 <table_name>pg_authid</table_name><column_name>rolsuper</column_name>
 ...
```

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

### GET DIAGNOSTICS example

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

An example of using GET DIAGNOSTICS to assign the ROW_COUNT status to an integer variable.

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

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

### SHOW ALL Example

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

Displays all configuration parameters along with their current settings and descriptions.

```sql
SHOW ALL;
            name         | setting |                description                                                          
-------------------------+---------+-------------------------------------------------
 allow_system_table_mods | off     | Allows modifications of the structure of ...
    .
    .
    .
 xmloption               | content | Sets whether XML data in implicit parsing ...
 zero_damaged_pages      | off     | Continues processing past damaged page headers.
(196 rows)
```

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

### xmlelement Examples

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

Examples showing basic element creation, attributes, and content concatenation.

```sql
SELECT xmlelement(name foo);

 xmlelement
------------
 <foo/>

SELECT xmlelement(name foo, xmlattributes('xyz' as bar));

    xmlelement
------------------
 <foo bar="xyz"/>

SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');

             xmlelement
-------------------------------------
 <foo bar="2007-01-26">content</foo>
```

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

### Build everything including documentation and contrib

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

Build the server, utilities, client applications, documentation, and additional modules.

```bash
**make world**
```

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

### Start the build process

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

Initiate the build using GNU make. You can use either make or make all.

```bash
**make**
**make all**
```

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

### Ignore harmless sepgsql installation notifications

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

Example of harmless warnings that may appear during installation depending on libselinux and selinux-policy versions.

```text
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 33 has invalid object type db_blobs
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 36 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 37 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 38 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 39 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 40 has invalid object type db_language
```

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

### SET CONNECTION Usage Examples

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

Examples showing how to switch to a specific database connection using the EXEC SQL prefix.

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

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

### Install only client applications

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

Install only the client applications and interface libraries to the specified directory.

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

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

### PREPARE Example

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

An example showing the preparation of a SQL statement stored in a host variable and its subsequent execution using descriptors.

```c
char *stmt = "SELECT * FROM test1 WHERE a = ? AND b = ?";

EXEC SQL ALLOCATE DESCRIPTOR outdesc;
EXEC SQL PREPARE foo FROM :stmt;

EXEC SQL EXECUTE foo USING SQL DESCRIPTOR indesc INTO SQL DESCRIPTOR outdesc;
```

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

### Create one-to-many relationship tables

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

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

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

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

### Variable Initialization Examples

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

Examples showing how to set default values and constants during variable declaration.

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

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

### OPEN command examples

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

Examples of using the EXEC SQL OPEN command with various binding methods.

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

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

### Permission denied error on reserved port

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

Example of a failure 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
```

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

### Address already in use error log

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

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

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

### Example postgresql.conf file content

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

A sample configuration file showing parameter settings, comments, and formatting rules.

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

```

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

### Get B-tree index statistics with pgstatindex

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

Example of using the pgstatindex function to retrieve structural information about a specific 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
```

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

### CREATE USER MAPPING Example

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

An example of creating a user mapping for user 'bob' on server 'foo' with specific credentials.

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

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

### OutputPluginWrite Example

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

Demonstrates the sequence of calling OutputPluginPrepareWrite, appending data to the buffer, and calling OutputPluginWrite to send data to the consumer.

```c
OutputPluginPrepareWrite(ctx, true);
appendStringInfo(ctx->out, "BEGIN %u", txn->xid);
OutputPluginWrite(ctx, true);
```

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

### DTrace Linking Error on Solaris

Source: https://www.postgresql.org/docs/14/installation-platform-notes.html

Example of a fatal linking error encountered when the DTrace installation is too old to handle probes in static functions.

```text
Undefined                       first referenced
 symbol                             in file
AbortTransaction                    utils/probes.o
CommitTransaction                   utils/probes.o
ld: fatal: Symbol referencing errors. No output written to postgres
collect2: ld returned 1 exit status
make: *** [postgres] Error 1
```

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

### Create database demo with default settings

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

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

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

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

### Get relation statistics with pgstattuple

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

Example of using the pgstattuple function to retrieve physical storage statistics for the pg_proc system catalog 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
```

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

### AIX CREATE EXTENSION plperl error as owner

Source: https://www.postgresql.org/docs/14/installation-platform-notes.html

Example of a memory address error when attempting to load an extension as the owner of the PostgreSQL installation on AIX.

```sql
=# CREATE EXTENSION plperl;
ERROR:  could not load library "/opt/dbs/pgsql/lib/plperl.so": A memory address is not in the address space for the process.
```

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

### Obtain Error Information with GET STACKED DIAGNOSTICS

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

An example showing how to capture error message text, detail, and hint information 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;
```

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

### Using SQLDA in ECPG

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

Complete example showing how to prepare a statement, describe it into an SQLDA, and fetch results.

```c
EXEC SQL INCLUDE sqlda.h;

    sqlda_t        *sqlda; /* This doesn't need to be under embedded DECLARE SECTION */

    EXEC SQL BEGIN DECLARE SECTION;
    char *prep_stmt = "select * from table1";
    int i;
    EXEC SQL END DECLARE SECTION;

    ...

    EXEC SQL PREPARE mystmt FROM :prep_stmt;

    EXEC SQL DESCRIBE mystmt INTO sqlda;

    printf("# of fields: %d\n", sqlda->sqld);
    for (i = 0; i < sqlda->sqld; i++)
      printf("field %d: \"%s\"\n", sqlda->sqlvar[i]->sqlname);

    EXEC SQL DECLARE mycursor CURSOR FOR mystmt;
    EXEC SQL OPEN mycursor;
    EXEC SQL WHENEVER NOT FOUND GOTO out;

    while (1)
    {
      EXEC SQL FETCH mycursor USING sqlda;
    }

    EXEC SQL CLOSE mycursor;

    free(sqlda); /* The main structure is all to be free(),
                  * sqlda and sqlda->sqlvar is in one allocated area */
```

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

### AIX CREATE EXTENSION plperl error as non-owner

Source: https://www.postgresql.org/docs/14/installation-platform-notes.html

Example of a bad address error when attempting to load an extension as a non-owner in the group possessing the PostgreSQL installation on AIX.

```sql
=# CREATE EXTENSION plperl;
ERROR:  could not load library "/opt/dbs/pgsql/lib/plperl.so": Bad address
```

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

### Obtaining Call Stack with PG_CONTEXT

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

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

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

### Split strings using regexp_split_to_table and regexp_split_to_array

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

Examples of splitting strings into rows or arrays using whitespace patterns. Note that zero-length matches at the start, end, or immediately after a match are ignored.

```sql
SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
  foo   
-------
 the    
 quick  
 brown  
 fox    
 jumps 
 over   
 the    
 lazy   
 dog    
(9 rows)

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
              regexp_split_to_array             
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
 foo 
-----
 t         
 h         
 e         
 q         
 u         
 i         
 c         
 k         
 b         
 r         
 o         
 w         
 n         
 f         
 o         
 x         
(16 rows)
```

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

### Implement Production Row-Level Security for passwd Table

Source: https://www.postgresql.org/docs/14/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;
```

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

### libpq Example Program 1 (testlibpq.c)

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

A comprehensive example demonstrating database connection, transaction management, cursor usage, and result retrieval using libpq. This program is located in the source distribution at src/test/examples/testlibpq.c.

```c

/*
 * src/test/examples/testlibpq.c
 *
 *
 * testlibpq.c
 *
 *      Test the C version of libpq, the PostgreSQL frontend library.
 */
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    int         nFields;
    int         i,
                j;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "%s", PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /* Set always-secure search path, so malicious users can't take control. */
    res = PQexec(conn,
                 "SELECT pg_catalog.set_config('search_path', '', false)");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /*
     * Should PQclear PGresult whenever it is no longer needed to avoid memory
     * leaks
     */
    PQclear(res);

    /*
     * Our test case here involves using a cursor, for which we must be inside
     * a transaction block.  We could do the whole thing with a single
     * PQexec() of "select * from pg_database", but that's too trivial to make
     * a good example.
     */

    /* Start a transaction block */
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    /*
     * Fetch rows from pg_database, the system catalog of databases
     */
    res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in myportal");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /* first, print out the attribute names */
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");

    /* next, print out the rows */
    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }

    PQclear(res);

    /* close the portal ... we don't bother to check for errors ... */
    res = PQexec(conn, "CLOSE myportal");
    PQclear(res);

    /* end the transaction */
    res = PQexec(conn, "END");
    PQclear(res);

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}
```

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

### CLOSE Example

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

An example of closing a cursor.

```sql
CLOSE curs1;
```

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

### SHOW geqo Example

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

Displays the current setting of the geqo parameter.

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

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

### Simple CASE Expression Example

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

An example showing the simple CASE syntax used to achieve the same result as the previous general CASE example.

```sql
SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other
```

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

### Install tsm_system_rows extension

Source: https://www.postgresql.org/docs/14/tsm-system-rows.html

Install the tsm_system_rows extension to enable the SYSTEM_ROWS sampling method. This module is trusted and can be installed by non-superusers with CREATE privilege.

```sql
CREATE EXTENSION tsm_system_rows;
```

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

### SET DESCRIPTOR Examples

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

Examples showing how to set the descriptor count, assign data values, and use host variables or indicators.

```sql
EXEC SQL SET DESCRIPTOR indesc COUNT = 1;
EXEC SQL SET DESCRIPTOR indesc VALUE 1 DATA = 2;
EXEC SQL SET DESCRIPTOR indesc VALUE 1 DATA = :val1;
EXEC SQL SET DESCRIPTOR indesc VALUE 2 INDICATOR = :val1, DATA = 'some string';
EXEC SQL SET DESCRIPTOR indesc VALUE 2 INDICATOR = :val2null, DATA = :val2;
```

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

### Rename old PostgreSQL installation directory

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

Rename the existing PostgreSQL installation directory to prevent interference with the new installation when using non-version-specific paths.

```bash
mv /usr/local/pgsql /usr/local/pgsql.old
```

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

### Managing Multiple Connections Example

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

A complete C program demonstrating how to connect to multiple databases and switch between them using AT and SET CONNECTION.

```c
#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
    char dbname[1024];
EXEC SQL END DECLARE SECTION;

int
main()
{
    EXEC SQL CONNECT TO testdb1 AS con1 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
    EXEC SQL CONNECT TO testdb2 AS con2 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
    EXEC SQL CONNECT TO testdb3 AS con3 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

    /* This query would be executed in the last opened database "testdb3". */
    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb3)\n", dbname);

    /* Using "AT" to run a query in "testdb2" */
    EXEC SQL AT con2 SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb2)\n", dbname);

    /* Switch the current connection to "testdb1". */
    EXEC SQL SET CONNECTION con1;

    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb1)\n", dbname);

    EXEC SQL DISCONNECT ALL;
    return 0;
}
```

```text
current=testdb3 (should be testdb3)
current=testdb2 (should be testdb2)
current=testdb1 (should be testdb1)
```

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

### PostgreSQL Source Installation Commands

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

A sequence of commands to compile, install, and initialize a PostgreSQL database. Requires root privileges for installation and user management steps.

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

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

### Initialize database cluster with sepgsql

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

Steps to configure shared_preload_libraries and install sepgsql functions into system databases.

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

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

### Create database demo with specific host, port, and template

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

Creates a database named demo on host eden at port 5000 using the template0 template. The example shows both the command-line utility invocation and the resulting SQL command.

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

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

### WHILE Loop Examples

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

Examples of WHILE loops checking boolean conditions.

```plpgsql
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT done LOOP
    -- some computations here
END LOOP;
```

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

### ltxtquery example

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

An example of an ltxtquery pattern for full-text-search-like matching.

```text
Europe & Russia*@ & !Transportation
```

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

### xmlpi Example

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

Example of creating an XML processing instruction.

```sql
SELECT xmlpi(name php, 'echo "hello world";');

            xmlpi
-----------------------------
 <?php echo "hello world";?>
```

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

### Pass parameters to postgres command

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

Shows how to pass configuration settings during server startup using the -c command-line parameter.

```bash
postgres -c log_connections=yes -c log_destination='syslog'
```

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

### Create a Foreign Server Example

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

An example of creating a server named 'myserver' that utilizes the 'postgres_fdw' foreign-data wrapper with specific connection options.

```sql
CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname 'foodb', port '5432');
```

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

### Full procedure example

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

A complete example demonstrating the execution of a SELECT statement, descriptor allocation, data retrieval, 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;
}
```

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

### IF-THEN-ELSIF Example

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

An example showing how to check multiple conditions sequentially.

```plpgsql
IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- hmm, the only other possibility is that number is null
    result := 'NULL';
END IF;
```

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

### IF-THEN Example

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

An example of an IF-THEN statement updating a user record.

```plpgsql
IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
```

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

### Main Program with PQexecParams

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

The main function demonstrating database connection, setting the search path, and executing a query with out-of-line text parameters and binary results.

```c
int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    const char *paramValues[1];
    int         paramLengths[1];
    int         paramFormats[1];
    uint32_t    binaryIntVal;

    /*
     * 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, "SET search_path = testlibpq3");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    /*
     * The point of this program is to illustrate use of PQexecParams() with
     * out-of-line parameters, as well as binary transmission of data.
     *
     * This first example transmits the parameters as text, but receives the
     * results in binary format.  By using out-of-line parameters we can avoid
     * a lot of tedious mucking about with quoting and escaping, even though
     * the data is text.  Notice how we don't have to do anything special with
     * the quote mark in the parameter value.
     */

    /* Here is our out-of-line parameter value */
    paramValues[0] = "joe's place";

    res = PQexecParams(conn,
                       "SELECT * FROM test1 WHERE t = $1",
                       1,       /* one param */
                       NULL,    /* let the backend deduce param type */
                       paramValues,
                       NULL,    /* don't need param lengths since text */
                       NULL,    /* default to all text params */
                       1);      /* ask for binary results */

    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    show_binary_results(res);

    PQclear(res);

    /*
     * In this second example we transmit an integer parameter in binary form,
     * and again retrieve the results in binary form.
     *
     * Although we tell PQexecParams we are letting the backend deduce
     * parameter type, we really force the decision by casting the parameter
     * symbol in the query text.  This is a good safety measure when sending
     * binary parameters.
     */
```

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

### Install documentation tools on FreeBSD

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

Use pkg to install the required packages. Note that gmake is required for building from the doc directory.

```bash
pkg install docbook-xml docbook-xsl libxslt fop
```

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

### Build everything excluding documentation

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

Build the server, utilities, client applications, and additional modules without documentation.

```bash
**make world-bin**
```

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

### Install PostgreSQL to a destination directory

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

Install the built files into the specified directory using the standard layout.

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

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

### Timestamp output example

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

Example output format for a timestamp variable.

```text
ts = 2010-06-27 18:03:56.949343
```

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

### Example output

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

The expected output when running the full procedure example.

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

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

### Create a realistic products table

Source: https://www.postgresql.org/docs/14/ddl-basics.html

Shows a more practical example of creating a table with descriptive names and a numeric type for monetary values.

```sql
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);
```

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

### Install postgres_fdw extension

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

Install the postgres_fdw extension in the local database.

```sql
CREATE EXTENSION postgres_fdw;
```

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

### Retrieve Configuration Parameter

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

Example of retrieving a configuration parameter using the SHOW command.

```sql
EXEC SQL SHOW search_path INTO :var;
```

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

### Start server using pg_ctl

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

Use the pg_ctl wrapper to start the server in the background and specify a log file.

```bash
pg_ctl start -l logfile
```

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

### postgresql.conf configuration for pg_prewarm

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

Example configuration for enabling the pg_prewarm module and setting the autoprewarm interval in postgresql.conf.

```text
# postgresql.conf
shared_preload_libraries = 'pg_prewarm'

pg_prewarm.autoprewarm = true
pg_prewarm.autoprewarm_interval = 300s
```

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

### crosstab3 usage example

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

An example of using the crosstab3 function to pivot data.

```sql
SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');
```

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

### Prepare directory and initialize cluster

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

Create the parent directory, assign ownership to the postgres user, and initialize the cluster as the postgres user.

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

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

### DROP SUBSCRIPTION Example

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

A basic example of dropping a subscription named mysub.

```sql
DROP SUBSCRIPTION mysub;
```

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

### Drop a policy example

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

Example of dropping a policy named 'p1' from 'my_table'.

```sql
DROP POLICY p1 ON my_table;
```

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

### Create and Populate Partition Before Inheritance

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

Create a child table using the structure of the parent, populate it, and then attach it to the hierarchy.

```sql
CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;
```

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

### DROP DOMAIN Example

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

An example showing how to remove a domain named 'box'.

```sql
DROP DOMAIN box;
```
Tokens:
Raw