PostgreSQL

https://www.postgresql.org/docs/current
Admin
PostgreSQL is an advanced open-source relational database system known for reliability, feature...
Tokens:891,275
Snippets:7,175
Trust Score:10
Update:2 weeks ago
Show doc for...
Tokens:
Raw
### Quick start build and installation commands

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

Performs a complete end-to-end setup, compile, install, database initialization, and startup sequence using Meson and Ninja.

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

```

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

### Create products table example

Source: https://www.postgresql.org/docs/current/dml-insert.html

Defines the sample products table used across the insertion examples.

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

```

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

### Start new PostgreSQL server with postgres

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

Starts the database server daemon on the newly initialized data directory under the database user account.

```shell
**/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data**

```

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

### Install PostgreSQL with ninja install

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

Installs built PostgreSQL files into the configured destination directories. Appropriate write permissions for target locations are required.

```shell
ninja install

```

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

### Configure build options with meson setup

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

Sets custom configuration parameters during setup, such as custom prefix paths, debug build types, or OpenSSL support.

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

```

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

### Initialize build directory with meson setup

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

Creates and configures the build tree for the current system using default settings.

```shell
**meson setup build**

```

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

### Install documentation tool packages on FreeBSD

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

Installs the required DocBook and XSL tools using pkg. When building documentation on FreeBSD, use gmake instead of FreeBSD make.

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

```

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

### Start psql in single-step mode and execute basics.sql

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

Start psql with the -s flag to pause before sending each SQL statement to the server. Use the \i command inside psql to read and execute the SQL commands from basics.sql.

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

...

mydb=> ** basics.sql**

```

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

### Install documentation tool packages on Debian

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

Installs DocBook XML, XSL stylesheets, libxml2-utils, xsltproc, and fop on Debian GNU/Linux using apt-get.

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

```

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

### Build and install contrib modules

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

Run in the contrib directory of a configured source tree or a specific module's subdirectory to build and install optional components.

```shell
**make**
**make install**

```

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

### Compile and Link Shared Library on macOS

Source: https://www.postgresql.org/docs/current/xfunc-c.html

Compiles object files and creates a loadable bundle using `-bundle -flat_namespace -undefined suppress`. Requires developer tools to be installed.

```shell
cc -c foo.c
cc -bundle -flat_namespace -undefined suppress -o foo.so foo.o

```

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

### Create rainbow enum type

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

Defines an enum type used as the prerequisite setup for enum support function examples.

```sql
CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');

```

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

### Get B-tree multi-page summary statistics with bt_multi_page_stats

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

Returns page statistics across a range of B-tree index pages starting at a specified block number. Pass a negative page count to scan all remaining pages to the end of the index.

```sql
test=# SELECT * FROM bt_multi_page_stats('pg_proc_oid_index', 5, 2);
-[ RECORD 1 ]-+-----
blkno         | 5
type          | l
live_items    | 367
dead_items    | 0
avg_item_size | 16
page_size     | 8192
free_size     | 808
btpo_prev     | 4
btpo_next     | 6
btpo_level    | 0
btpo_flags    | 1
-[ RECORD 2 ]-+-----
blkno         | 6
type          | l
live_items    | 367
dead_items    | 0
avg_item_size | 16
page_size     | 8192
free_size     | 808
btpo_prev     | 5
btpo_next     | 7
btpo_level    | 0
btpo_flags    | 1

```

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

### Connect to PostgreSQL and fetch cursor data using libpq (testlibpq.c)

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

Demonstrates connecting to PostgreSQL using PQconnectdb, checking connection status, setting a secure search path, and fetching database rows via a cursor transaction.

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

```

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

### Run contrib regression tests before installation

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

Executes regression tests for contrib modules prior to installing them.

```shell
**make check**

```

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

### CREATE SERVER command syntax synopsis

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

Synopsis showing the command structure and available options for defining a new foreign server.

```sql
CREATE SERVER [ IF NOT EXISTS ] _server_name_ [ TYPE '_server_type_' ] [ VERSION '_server_version_' ]
    FOREIGN DATA WRAPPER _fdw_name_
    [ OPTIONS ( _option_ '_value_' [, ... ] ) ]

```

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

### Show all configuration settings

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

Lists the names, current settings, and descriptions for all configuration parameters.

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

```

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

### Install documentation tool packages on macOS with Homebrew

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

Installs DocBook, DocBook XSL, libxslt, and FOP using Homebrew on macOS.

```shell
brew install docbook docbook-xsl libxslt fop

```

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

### Install documentation tool packages on Fedora and RHEL

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

Installs the required DocBook DTDs, stylesheets, libxslt, and FOP packages using yum.

```shell
yum install docbook-dtds docbook-style-xsl libxslt fop

```

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

### Create a new database

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

Creates a standard database using default settings.

```sql
CREATE DATABASE lusiadas;

```

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

### Build tutorial scripts in the PostgreSQL source distribution

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

Navigate to the src/tutorial/ directory and run make to generate scripts and compile C files for user-defined functions and types. Binary distributions may not include these files.

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

```

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

### Install documentation tool packages on macOS with MacPorts

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

Installs the required DocBook DTD, non-namespaced XSL stylesheets, libxslt, and FOP using MacPorts.

```shell
sudo port install docbook-xml docbook-xsl-nons libxslt fop

```

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

### Connect, prepare statement, and declare cursor

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

Establishes a connection to the database, sets the search path, prepares a dynamic SQL statement with placeholders, and declares a cursor for it.

```c
int
main(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
    char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";
    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 PREPARE stmt1 FROM :query;
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

```

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

### Execute Dynamic SQL with EXECUTE IMMEDIATE and Host Variable

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

Demonstrates preparing an INSERT SQL statement dynamically in a host variable and running it immediately with EXEC SQL EXECUTE IMMEDIATE.

```c
sprintf(command, "INSERT INTO test (name, amount, letter) VALUES ('db: ''r1''', 1, 'f')");
EXEC SQL EXECUTE IMMEDIATE :command;

```

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

### Establish database connections with \c

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

Connect to a PostgreSQL server using positional parameters, service names, conninfo strings, connection parameter reuse flags, or 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

```

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

### Install extension into a specific schema with SCHEMA clause

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

Installs the hstore extension into the current database, placing its objects in the addons schema using the explicit SCHEMA option.

```sql
CREATE EXTENSION hstore SCHEMA addons;

```

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

### Rename the old PostgreSQL installation directory

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

Renames the existing non-version-specific installation directory as a single unit so relative paths remain intact and the old version can be reverted if needed.

```shell
**mv /usr/local/pgsql /usr/local/pgsql.old**

```

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

### Create Parent Directory and Initialize Cluster with initdb

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

Creates and sets ownership of the parent directory using root privileges before switching to the postgres user to run initdb. initdb will fail if it lacks write permissions in the parent directory.

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

```

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

### PQcancelStart

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

Starts an asynchronous cancel request over a given PGcancelConn object. Returns 1 if the cancellation request could be started and 0 if it failed, in which case error details can be retrieved via PQcancelErrorMessage.

```APIDOC
## PQcancelStart

### Description
Starts an asynchronous cancel request on the given cancel connection object.

### Syntax
```c
int PQcancelStart(PGcancelConn *cancelConn);
```

### Parameters
- **cancelConn** (`PGcancelConn *`) - Pointer to the cancel connection object created with `PQcancelCreate`.

### Return Value
- Returns `1` if the cancellation request could be successfully started.
- Returns `0` if unsuccessful. Error details can be retrieved using `PQcancelErrorMessage`.
```

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

### EXPLAIN statement syntax synopsis

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

Shows the general command syntax and available options for configuring planner output, execution analytics, and output formats.

```sql
EXPLAIN [ ( _option_ [, ...] ) ] _statement_

where _option_ can be one of:

    ANALYZE [ _boolean_ ]
    VERBOSE [ _boolean_ ]
    COSTS [ _boolean_ ]
    SETTINGS [ _boolean_ ]
    GENERIC_PLAN [ _boolean_ ]
    BUFFERS [ _boolean_ ]
    SERIALIZE [ { NONE | TEXT | BINARY } ]
    WAL [ _boolean_ ]
    TIMING [ _boolean_ ]
    SUMMARY [ _boolean_ ]
    MEMORY [ _boolean_ ]
    FORMAT { TEXT | XML | JSON | YAML }
```
Tokens:
Raw