PostgreSQL

https://www.postgresql.org/docs/current/
Admin
PostgreSQL is a powerful, open-source object-relational database system known for its reliability,...
Tokens:2,455,556
Snippets:19,437
Trust Score:10
Update:1 day ago
Show doc for...
Tokens:
Raw
### meson setup with Common Options

Source: https://www.postgresql.org/docs/current/install-meson.html

Examples of configuring the build with different installation prefix, build type, and OpenSSL support. Each command is a separate configuration choice.

```shell
# configure with a different installation prefix
meson setup build --prefix=/home/user/pg-install

# configure to generate a debug build
meson setup build --buildtype=debug

# configure to build with OpenSSL support
meson setup build -Dssl=openssl
```

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

### Start psql and show prompt

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

Example of starting psql with a database name, showing the version banner and the interactive prompt (testdb=>). The prompt indicates the current database and that psql is ready for input.

```shell
$ psql testdb
psql (18.6)
Type "help" for help.

testdb=>
```

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

### Short Version Installation Commands

Source: https://www.postgresql.org/docs/current/install-make.html

Quick installation commands for PostgreSQL using Autoconf and Make. Includes configuration, build, installation, and initial database setup.

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

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

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

```sql
CREATE TYPE hstore ...;

CREATE EXTENSION plpython3u;
```

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

### Build tutorial examples with make

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

Run from the src/tutorial/ directory to create scripts and compile C files for user-defined functions and types.

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

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

### pg_get_wal_records_info example

Source: https://www.postgresql.org/docs/current/pgwalinspect.html

Shows how to call pg_get_wal_records_info with start and end LSNs to get one row per valid WAL record in that range. The example uses LIMIT 1 to show a single record. The function raises an error if start_lsn is not available.

```sql
postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;
-[ RECORD 1 ]----+--------------------------------------------------------------
start_lsn        | 0/1E913618
end_lsn          | 0/1E913650
prev_lsn         | 0/1E9135A0
xid              | 0
resource_manager | Standby
record_type      | RUNNING_XACTS
record_length    | 50
main_data_length | 24
fpi_length       | 0
description      | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 33775
block_ref        |
```

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

### make install-docs - Install documentation

Source: https://www.postgresql.org/docs/current/install-make.html

Installs the HTML and man page documentation.

```shell
make install-docs
```

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

### GET DIAGNOSTICS example with ROW_COUNT

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

Example of using GET DIAGNOSTICS to assign the number of rows processed by the most recent SQL command to a variable.

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

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

### pg_get_wal_record_info example

Source: https://www.postgresql.org/docs/current/pgwalinspect.html

Shows how to call pg_get_wal_record_info with an LSN to get information about the WAL record at or after that LSN. The output includes fields like start_lsn, end_lsn, resource_manager, and record_type. If the LSN is not at the start of a record, the next valid record is shown; if none exists, an error is raised.

```sql
postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');
-[ RECORD 1 ]----+-------------------------------------------------
start_lsn        | 0/E419E28
end_lsn          | 0/E419E68
prev_lsn         | 0/E419D78
xid              | 0
resource_manager | Heap2
record_type      | VACUUM
record_length    | 58
main_data_length | 2
fpi_length       | 0
description      | nunused: 5, unused: [1, 2, 3, 4, 5]
block_ref        | blkref #0: rel 1663/16385/1249 fork main blk 364
```

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

### date_bin examples with results

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

Examples of date_bin with different origins, showing the resulting bin start times.

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

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

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

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

This C program demonstrates a complete libpq workflow: connecting to a database, setting a secure search path, starting a transaction, declaring a cursor, fetching rows, and cleaning up. It uses PQconnectdb, PQexec, PQresultStatus, PQclear, and PQfinish. The program accepts an optional conninfo string as a command-line argument; otherwise it defaults to 'dbname = postgres'. It prints attribute names and row values from the pg_database catalog.

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

```

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

### Create database demo with default server

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

Creates the database 'demo' using the default database server.

```shell
$ createdb demo
```

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

### PL/pgSQL example using GET DIAGNOSTICS PG_CONTEXT

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

Shows how to capture the call stack with GET DIAGNOSTICS stack = PG_CONTEXT and raise it as a NOTICE. The example defines outer_func and inner_func, then calls outer_func to display the stack trace.

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

Source: https://www.postgresql.org/docs/current/install-make.html

Commands to start the build. Use 'make' or 'make all' to build the server and utilities. Remember to use GNU make.

```shell
make
make all
```

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

### regexp_instr examples

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

Returns the starting or ending position of the N'th match. The second example uses named parameters and the 'i' flag.

```sql
regexp_instr('number of your street, town zip, FR', '[^,]+', 1, 2)
                                   23
regexp_instr(string=>'ABCDEFGHI', pattern=>'(c..)(...)', start=>1, "N"=>1, endoption=>0, flags=>'i', subexpr=>2)
                                   6
```

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

### SHOW ALL - Display All Settings

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

Lists all configuration parameters and their current settings. The output is truncated in the example, showing a subset of the 196 rows returned.

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

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

### pgstatindex example query

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

Example of using pgstatindex to get B-tree index statistics. The output shows version, tree_level, index_size, and other index-related metrics.

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

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

### Prepare and Execute with SQL Descriptors

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

Example showing how to allocate a descriptor, prepare a statement with placeholders, and execute it using input and output descriptors. The statement uses '?' placeholders for parameters.

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

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

### PL/pgSQL example using GET STACKED DIAGNOSTICS to capture error details

Source: https://www.postgresql.org/docs/current/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. The ellipsis (...) represents processing that might cause an exception.

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

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

### Short Version: Complete Installation Commands

Source: https://www.postgresql.org/docs/current/install-meson.html

A quick-start sequence of commands to configure, build, install, and initialize a PostgreSQL cluster using Meson. Run as root for installation steps, then as the postgres user for database initialization and access.

```shell
meson setup build --prefix=/usr/local/pgsql
cd build
ninja
su
ninja 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
```

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

### pg_get_wal_block_info example

Source: https://www.postgresql.org/docs/current/pgwalinspect.html

Shows how to call pg_get_wal_block_info with start and end LSNs to get one row per block reference per WAL record. The output includes block-level details like block_id, relfilenode, and block_data. Rows are guaranteed to have a unique combination of start_lsn and block_id. Records with no block references (e.g., COMMIT) return no rows.

```sql
postgres=# SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');
-[ RECORD 1 ]-----+-----------------------------------
start_lsn         | 0/1230278
end_lsn           | 0/12302B8
prev_lsn          | 0/122FD40
block_id          | 0
reltablespace     | 1663
reldatabase       | 1
relfilenode       | 2658
relforknumber     | 0
relblocknumber    | 11
xid               | 341
resource_manager  | Btree
record_type       | INSERT_LEAF
record_length     | 64
main_data_length  | 2
block_data_length | 16
block_fpi_length  | 0
block_fpi_info    |
description       | off: 46
block_data        | \x00002a00070010402630000070696400
block_fpi_data    |
```

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

### make install-world - Install world build

Source: https://www.postgresql.org/docs/current/install-make.html

Installs everything built with 'make world', including documentation.

```shell
make install-world
```

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

### pgstattuple example query

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

Example of using pgstattuple to get tuple-level statistics for a relation. The output shows columns like table_len, tuple_count, dead_tuple_count, and free_space.

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

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

### Install extension with non-postgres prefix

Source: https://www.postgresql.org/docs/current/extend-pgxs.html

When the prefix does not contain 'postgres' or 'pgsql', 'postgresql' is appended to the directory names. This example installs control and SQL files into /usr/local/extras/share/postgresql/extension and shared modules into /usr/local/extras/lib/postgresql.

```makefile
make install prefix=/usr/local/extras
```

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

### Example: connect, open, and close a cursor

Source: https://www.postgresql.org/docs/current/contrib-dblink-close.html

Shows a complete workflow: connect to a database, open a cursor, then close it. Each command returns OK, demonstrating successful execution.

```sql
SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
 dblink_open
-------------
 OK
(1 row)

SELECT dblink_close('foo');
 dblink_close
--------------
 OK
(1 row)
```

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

### OPEN Examples

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

Illustrates opening cursors with different binding methods: no values, direct values, SQL descriptor, and host variable for cursor name.

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

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

### Examples of current date/time functions

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

Shows example outputs for CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(2), and LOCALTIMESTAMP. These functions return the start time of the current transaction and do not change during the transaction.

```sql
SELECT CURRENT_TIME;
Result: 14:39:53.662522-05
SELECT CURRENT_DATE;
Result: 2019-12-23
SELECT CURRENT_TIMESTAMP;
Result: 2019-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
Result: 2019-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
Result: 2019-12-23 14:39:53.662522
```

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

### SET search_path and CREATE EXTENSION hstore

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

Alternative way to install hstore into the 'addons' schema by first setting the search_path to 'addons' and then creating the extension.

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

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

### Create composite type and table for ECPG examples

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

Defines the composite type comp_t and table t4 used in the following ECPG examples. This setup is required before running the retrieval programs.

```sql
CREATE TYPE comp_t AS (intval integer, textval varchar(32));
CREATE TABLE t4 (compval comp_t);
INSERT INTO t4 VALUES ( (256, 'PostgreSQL') );

```

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

### Example 1: Activate subscription on subscriber

Source: https://www.postgresql.org/docs/current/logical-replication-subscription.html

Enables the subscription and refreshes the publication to start replication.

```sql
/* sub # */ ALTER SUBSCRIPTION sub1 ENABLE;
/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
```

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

### Example: create table and query dblink_get_pkey

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

Creates a table with a composite primary key and then queries dblink_get_pkey to show the positions and column names. The output shows three rows for the three primary key fields.

```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)
```
Tokens:
Raw