PostgreSQL

https://www.postgresql.org/docs/15/
Admin
PostgreSQL is a powerful, open-source object-relational database system known for reliability,...
Tokens:2,341,062
Snippets:18,611
Trust Score:10
Update:3 hours ago
Show doc for...
Tokens:
Raw
### Short Version Installation Commands

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

Run these commands in order to compile, install, and set up a basic PostgreSQL 15 installation. The sequence includes creating a system user, initializing the data directory, starting the server, and creating a test database.

```shell
./configure
make
su
make install
adduser postgres
mkdir -p /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
```

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

### Set up type and language for transform

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

First step in the example: create the type hstore and install the plpython3u extension.

```sql
CREATE TYPE hstore ...;

CREATE EXTENSION plpython3u;
```

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

### GET DIAGNOSTICS example with ROW_COUNT

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

Example of using GET DIAGNOSTICS to assign the ROW_COUNT status item to a variable.

```sql
GET DIAGNOSTICS integer_var = ROW_COUNT;
```

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

### Install world with make install-world

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

Installs everything including documentation. Use this if you built the world.

```shell
make install-world
```

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

### Install documentation with make install-docs

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

Installs the HTML and man page documentation.

```shell
make install-docs
```

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

### GET STACKED DIAGNOSTICS example

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

Example of using GET STACKED DIAGNOSTICS inside an EXCEPTION WHEN OTHERS handler to capture the error message, detail, and hint into text variables.

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

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

### Build with make or make all

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

Start the build by typing either 'make' or 'make all'. Use GNU make.

```bash
make
```

```bash
make all
```

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

### generate_series integer examples

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

Examples of generate_series with integer arguments, including positive and negative steps, and zero rows when start exceeds stop.

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

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

### PL/pgSQL GET DIAGNOSTICS PG_CONTEXT call stack example

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

Shows how to capture the current call stack using GET DIAGNOSTICS with PG_CONTEXT. The example defines outer_func and inner_func, where inner_func retrieves the stack and raises a notice. The output demonstrates the stack trace lines and the final result.

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

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

### Build tutorial examples with make

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

Run make in the src/tutorial directory to create scripts and compile C files for user-defined functions and types. This is a prerequisite for using the tutorial files.

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

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

### Start postgres with a specific port (e.g., 1234)

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

Starts the PostgreSQL server on a specific port using the -p option. The example uses port 1234.

```shell
$ postgres -p 1234

```

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

### Build and install sepgsql-regtest policy package

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

Commands to build the sepgsql-regtest policy from source and install it using semodule. The Makefile path is an example; the actual path may vary. After installation, semodule -l should list sepgsql-regtest.

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

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

### libpq Example Program 1: Connect, Query, and Fetch with Cursor

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

This complete C program demonstrates basic libpq usage: connecting with PQconnectdb, checking connection status, setting a secure search path, starting a transaction, declaring a cursor, fetching rows, and printing results. It includes error handling with exit_nicely and proper cleanup with PQclear and PQfinish. The program accepts an optional conninfo string as a command-line argument, defaulting to 'dbname = postgres'.

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

```

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

### Install PostgreSQL to a destination directory

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

Run this command to install all files using the standard layout and generate the files required to initialize and use the database.

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

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

### Example table of contents list

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

Shows the format of the listing file generated by pg_restore -l. Semicolons start comments, and numbers at the start of lines are internal archive IDs.

```text
;
; Archive created at Mon Sep 14 13:55:39 2009
;     dbname: DBDEMOS
;     TOC Entries: 81
;     Compression: 9
;     Dump Version: 1.10-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 8.3.5
;     Dumped by pg_dump version: 8.3.8
;
;
; Selected TOC Entries:
;
3; 2615 2200 SCHEMA - public pasha
1861; 0 0 COMMENT - SCHEMA public pasha
1862; 0 0 ACL - public pasha
317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha
```

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

### Install world binaries with make install-world-bin

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

Installs the world without documentation. Use this if you built the world without documentation.

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

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

### Example role setup with INHERIT and NOINHERIT

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

Shows creating roles with different INHERIT attributes and granting membership. This example illustrates how privileges are inherited based on the INHERIT attribute.

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

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

### Example: print_sqlca() with WHENEVER SQLERROR

Source: https://www.postgresql.org/docs/15/ecpg-errors.html

An example combining WHENEVER SQLERROR and sqlca to print the contents of sqlca when an error occurs. Useful for debugging or prototyping before installing a user-friendly error handler.

```c
EXEC SQL WHENEVER SQLERROR CALL print_sqlca();

void
print_sqlca()
{
    fprintf(stderr, "==== sqlca ====\n");
    fprintf(stderr, "sqlcode: %ld\n", sqlca.sqlcode);
    fprintf(stderr, "sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
    fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    fprintf(stderr, "sqlerrd: %ld %ld %ld %ld %ld %ld\n", sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2],
                                                          sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]);
    fprintf(stderr, "sqlwarn: %d %d %d %d %d %d %d %d\n", sqlca.sqlwarn[0], sqlca.sqlwarn[1], sqlca.sqlwarn[2],
                                                          sqlca.sqlwarn[3], sqlca.sqlwarn[4], sqlca.sqlwarn[5],
                                                          sqlca.sqlwarn[6], sqlca.sqlwarn[7]);
    fprintf(stderr, "sqlstate: %5s\n", sqlca.sqlstate);
    fprintf(stderr, "===============\n");
}
```

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

### Run configure for default installation

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

Run the configure script to set up the source tree for a default installation. This is the first step of the installation procedure.

```bash
./configure
```

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

### pgstathashindex example query

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

Example query to get statistics for a HASH index using pgstathashindex. The output shows version, bucket_pages, overflow_pages, bitmap_pages, unused_pages, live_items, dead_items, and free_percent.

```sql
test=> select * from pgstathashindex('con_hash_index');
-[ RECORD 1 ]--+-----------------
version        | 4
bucket_pages   | 33081
overflow_pages | 0
bitmap_pages   | 1
unused_pages   | 32455
live_items     | 10204006
dead_items     | 0
free_percent   | 61.8005949100872
```

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

### Create database demo with default server

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

Creates the database 'demo' using the default database server. The database name is taken from the command line argument.

```shell
$ createdb demo
```

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

### Create data directory and run initdb

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

Recommended process: create the parent directory, change ownership to the postgres user, switch to that user, and run initdb. initdb will fail if the data directory exists and already contains files.

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

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

### pgstattuple_approx example query

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

Example query to get approximate statistics for a relation using pgstattuple_approx. The output shows table_len, scanned_percent, approx_tuple_count, approx_tuple_len, approx_tuple_percent, dead_tuple_count, dead_tuple_len, dead_tuple_percent, approx_free_space, and approx_free_percent.

```sql
test=> SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);
-[ RECORD 1 ]--------+-------
table_len            | 573440
scanned_percent      | 2
approx_tuple_count   | 2740
approx_tuple_len     | 561210
approx_tuple_percent | 97.87
dead_tuple_count     | 0
dead_tuple_len       | 0
dead_tuple_percent   | 0
approx_free_space    | 11996
approx_free_percent  | 2.09
```

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

### raise_pay function using array get for composite result

Source: https://www.postgresql.org/docs/15/pltcl-functions.html

Tip example showing how to build a composite result from an array representation using the Tcl 'array get' command. Modifies the employee array and returns it.

```sql
CREATE FUNCTION raise_pay(employee, delta int) RETURNS employee AS $$
    set 1(salary) [expr {$1(salary) + $2}]
    return [array get 1]
$$ LANGUAGE pltcl;
```

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

### Example: Create table and query primary key

Source: https://www.postgresql.org/docs/15/contrib-dblink-get-pkey.html

Shows creating a table with a composite primary key and then calling dblink_get_pkey to list the key fields. The output shows the position and column names.

```sql
CREATE TABLE foobar (
    f1 int,
    f2 int,
    f3 int,
    PRIMARY KEY (f1, f2, f3)
);
CREATE TABLE

SELECT * FROM dblink_get_pkey('foobar');
 position | colname
----------+---------
        1 | f1
        2 | f2
        3 | f3
(3 rows)
```

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

### Build plain text installation instructions

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

Run from doc/src/sgml to recreate the INSTALL file as plain text. Requires Pandoc version 1.13 or newer.

```shell
doc/src/sgml$ make INSTALL
```

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

### Example postgresql.conf contents

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

Shows a sample postgresql.conf file with comments, boolean, string, and size parameters. Demonstrates the syntax: one parameter per line, optional equal sign, single quotes for non-identifiers, and comments with #.

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

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

### Translator comment example

Source: https://www.postgresql.org/docs/15/nls-programmer.html

Shows how to add a comment for translators, starting with 'translator', which is copied to message catalog files.

```c
/* translator: This message is not what it seems to be. */
```

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

### date_bin examples with results

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

Two examples of date_bin usage with different origins, each followed by its result. Demonstrates binning to a 15-minute stride.

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

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

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

### Create composite type and table for ECPG examples

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

Defines a composite type comp_t with an integer and a varchar, 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') );

```

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

### Join pg_locks with pg_stat_activity

Source: https://www.postgresql.org/docs/15/view-pg-locks.html

Example query to join pg_locks with pg_stat_activity on the pid column to get session information for each lock.

```sql
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;
```

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

### Example: SELECT * FROM items_sold

Source: https://www.postgresql.org/docs/15/queries-table-expressions.html

Shows the initial data in the items_sold table used for the grouping examples.

```sql
=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)
```
Tokens:
Raw