PostgreSQL

https://www.postgresql.org/docs/16/
Admin
PostgreSQL is a powerful, open-source relational database system that supports advanced SQL...
Tokens:2,212,986
Snippets:22,894
Trust Score:10
Update:1 day ago
Show doc for...
Tokens:
Raw
### Configure with common meson setup options

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

Examples of passing build options to meson setup: custom installation prefix, debug build, and OpenSSL support.

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

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

### Quick build and install with Meson

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

Short version of the build and installation procedure using Meson, Ninja, and basic PostgreSQL setup commands.

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

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

### Short Version Installation Commands

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

A quick sequence of commands to configure, build, and install PostgreSQL, then initialize a database cluster and start the server. Run as a user with appropriate privileges; some steps require root or postgres user.

```bash
./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
```

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

### date_bin examples

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

Examples of date_bin with a 15-minute stride and 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
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
```

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

### Install PostgreSQL with ninja install

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

Run after building PostgreSQL to install files into the directories specified during meson setup. Ensure you have appropriate write permissions; you may need to run as root.

```shell
ninja install
```

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

### Example 1: Enable and refresh subscription sub1

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

On the subscriber, enable the subscription and refresh it to start replicating tables from pub1.

```sql
test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
ALTER SUBSCRIPTION
test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
ALTER SUBSCRIPTION
```

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

### Binary I/O and Out-of-Line Parameters Example (testlibpq3.c) - Part 1

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

First part of testlibpq3.c, showing the header comment with setup SQL and expected output, plus includes. The program demonstrates out-of-line parameters and binary I/O.

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

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

### Get current date and time with localtimestamp

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

Returns the current date and time (start of current transaction) as a timestamp without time zone. Example output includes microseconds.

```sql
localtimestamp
```

```sql
2019-12-23 14:39:53.662522
```

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

### Install documentation with make install-docs

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

Installs HTML and man page documentation. Use after building the documentation.

```bash
make install-docs
```

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

### GET DIAGNOSTICS Example: ROW_COUNT

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

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

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

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

### Get current date and time with limited precision using localtimestamp(integer)

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

Returns the current date and time (start of current transaction) with precision limited to the specified number of fractional seconds digits. Example uses precision 2.

```sql
localtimestamp(2)
```

```sql
2019-12-23 14:39:53.66
```

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

### Install documentation tools on macOS with Homebrew

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

Use brew install to install docbook, docbook-xsl, libxslt, and fop. After installation, set XML_CATALOG_FILES as shown in the following examples.

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

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

### PL/pgSQL example using GET STACKED DIAGNOSTICS

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

This example shows how to retrieve the error message, detail, and hint from the current exception using GET STACKED DIAGNOSTICS within an exception handler.

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

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

Start the build process using GNU make. Use 'make' or 'make all' to build the server and utilities.

```bash
make
make all
```

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

### Install world with make install-world

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

Installs all built components including documentation. Use if you built the world with documentation.

```bash
make install-world
```

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

### Example program managing multiple connections

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

Demonstrates connecting to three databases, using AT to query a specific connection, and switching the current connection with SET CONNECTION. Output shows the current database for each query.

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

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

### Initialize a database cluster with sepgsql

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

This example sets PGDATA, runs initdb, edits postgresql.conf to set shared_preload_libraries = 'sepgsql', and loads sepgsql.sql into template0, template1, and postgres using single-user mode. Adjust paths as needed for your installation.

```shell
$ 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 composite type and table for examples

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

Defines a composite type comp_t and a table t4, then inserts a sample row. Use this setup before running the 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') );
```

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

### Install world without documentation with make install-world-bin

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

Installs all built components but not documentation. Use if you built the world without documentation.

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

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

### Union of distributors and actors names starting with W

Source: https://www.postgresql.org/docs/16/sql-select.html

Combines names from distributors and actors that start with 'W' using UNION (distinct rows). The example shows sample data from both tables and the combined result.

```sql
distributors:               actors:
 did |     name              id |     name
-----+--------------        ----+----------------
 108 | Westward               1 | Woody Allen
 111 | Walt Disney            2 | Warren Beatty
 112 | Warner Bros.           3 | Walter Matthau
 ...                         ...

SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';

      name
----------------
 Walt Disney
 Walter Matthau
 Warner Bros.
 Warren Beatty
 Westward
 Woody Allen
```

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

### Create database demo with host, port, and template0

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

Use -p, -h, and -T to specify port 5000, host eden, and template0; -e echoes the generated SQL command.

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

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

### Create database demo using default server

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

Run createdb with the database name to create it on the default PostgreSQL server.

```shell
$ createdb demo
```

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

### Build tutorial scripts from src/tutorial with make

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

Run from the PostgreSQL source distribution's src/tutorial/ directory to create the scripts and compile the C files containing user-defined functions and types. Binary distributions might not provide these files.

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

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

### Example table of contents listing file

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

Shows the format of a pg_restore -l output file, with a header and one line per item. Semicolons start comments, and numbers at line starts 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 hstore Extension Using SET search_path

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

Alternative to CREATE EXTENSION ... SCHEMA: sets search_path to addons, then installs hstore so its objects go into that schema.

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

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

### Build Everything Including Documentation and Contrib

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

Build all components including documentation (HTML and man pages) and additional modules (contrib).

```bash
make world
```

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

### Get current date and time with now()

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

Returns the current date and time (start of current transaction) as timestamp with time zone.

```sql
now()
```

```sql
2019-12-23 14:39:53.662522-05
```

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

### Get current date and time with transaction_timestamp()

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

Returns the current date and time (start of current transaction) as timestamp with time zone.

```sql
transaction_timestamp()
```

```sql
2019-12-23 14:39:53.662522-05
```

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

### Get current date and time with statement_timestamp()

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

Returns the current date and time (start of current statement) as timestamp with time zone.

```sql
statement_timestamp()
```

```sql
2019-12-23 14:39:53.662522-05
```

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

### SET CONNECTION examples

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

Examples of using SET CONNECTION with TO and = to switch to connections con2 and con1.

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

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

### LogicalDecodeBeginCB

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

The required begin_cb callback is called whenever a start of a committed transaction has been decoded. Aborted transactions and their contents never get decoded.

```APIDOC
## LogicalDecodeBeginCB

### Description
The required `begin_cb` callback is called whenever a start of a committed transaction has been decoded. Aborted transactions and their contents never get decoded.

### Method
Callback function pointer

### Signature
```c
typedef void (*LogicalDecodeBeginCB) (struct LogicalDecodingContext *ctx,
                                      ReorderBufferTXN *txn);
```

### Parameters
- **ctx** (LogicalDecodingContext*) - Logical decoding context.
- **txn** (ReorderBufferTXN*) - Contains meta information about the transaction, like the time stamp at which it has been committed and its XID.
```

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

### Example postgresql.conf configuration file

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

An example of a postgresql.conf file showing parameter assignments, comments, and quoting. One parameter per line; equal sign optional; values with special characters must be single-quoted.

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

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

### Example: create table and query primary key with dblink_get_pkey

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

Creates a table foobar with a composite primary key and then calls dblink_get_pkey to retrieve the key columns. 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)
```

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

### Get current time of day with localtime

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

Returns the current time of day as a time value. Example output shows microsecond precision.

```sql
localtime
```

```sql
14:39:53.662522
```
Tokens:
Raw