PostgreSQL

https://www.postgresql.org/docs/19/
Admin
PostgreSQL is an open-source object-relational database management system designed to reliably store...
Tokens:2,711,196
Snippets:22,305
Trust Score:10
Update:1 day ago
Show doc for...
Tokens:
Raw
### Common meson setup options

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

Examples of configuring with a different installation prefix, generating a debug build, and building with 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
```

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

### Short version of Meson build and install commands

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

Complete sequence of commands to configure, build, install, and initialize a PostgreSQL cluster using Meson. Includes creating a data directory, starting the server, and creating a test database.

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

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

### Equivalent LATERAL FROM-clause query

Source: https://www.postgresql.org/docs/19/xfunc-sql.html

Shows the LATERAL FROM-clause equivalent of the previous example, with a note about possible different output row order.

```sql
SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
```

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

### Set-returning function in CASE (invalid)

Source: https://www.postgresql.org/docs/19/xfunc-sql.html

Shows an example that would produce a parse-time error because set-returning functions cannot be used in conditional constructs like CASE.

```sql
SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
```

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

### Create variadic function mleast and call it

Source: https://www.postgresql.org/docs/19/xfunc-sql.html

Declares a SQL function mleast that takes a VARIADIC numeric array and returns the minimum value. The example includes a call with multiple arguments and the resulting output.

```sql
CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT mleast(10, -1, 5, 4.4);
 mleast
--------
     -1
(1 row)
```

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

### Pipeline Mode Example

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

Shows a complete psql pipeline: start pipeline, execute queries, bind and send, flush request, get results, and end pipeline. COPY is not supported in pipeline mode.

```sql
\startpipeline
SELECT * FROM pg_class;
SELECT 1 \bind \sendpipeline
\flushrequest
\getresults
\endpipeline
```

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

### Build tutorial examples with make

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

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

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

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

### Start server at boot using su postgres

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

Example command for boot-time startup, using su postgres -c to run pg_ctl as the PostgreSQL user account. The server must be run by the PostgreSQL user, not root or any other user.

```shell
su postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog'
```

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

### GET DIAGNOSTICS example with ROW_COUNT

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

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

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

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

### Example of GET STACKED DIAGNOSTICS in exception handler

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

Example PL/pgSQL block that uses GET STACKED DIAGNOSTICS to capture MESSAGE_TEXT, PG_EXCEPTION_DETAIL, and PG_EXCEPTION_HINT into text variables when any exception occurs.

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

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

### \connect examples: various connection syntaxes

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

Examples of the \connect meta-command using positional syntax, conninfo strings, URI format, and the -reuse-previous option. The last example changes only the sslmode parameter.

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

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

### date_bin examples with results

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

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

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

### Install documentation with make install-docs

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

Installs the HTML and man pages documentation.

```shell
make install-docs
```

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

### regexp_replace examples

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

Examples of regexp_replace with different flags and parameters, including the 'g' flag, backreference, start position, and named arguments.

```sql
regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')
                                   fooXarYXazY
regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i')
                                   X PXstgrXSQL fXnctXXn
regexp_replace(string=>'A PostgreSQL function', pattern=>'a|e|i|o|u', replacement=>'X', start=>1, "N"=>3, flags=>'i')
                                   A PostgrXSQL function
```

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

### Build with make

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

Start the build with either 'make' or 'make all'. Use GNU make.

```shell
make
make all
```

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

### Expected output of GET DESCRIPTOR example

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

The output produced when the complete example is executed, showing the retrieved column count, data length, and data.

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

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

### Start psql and load basics.sql

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

Use psql with -s for single step mode and \i to read commands from basics.sql.

```shell
$ psql -s mydb

...

mydb=> \i basics.sql
```

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

### ECPG typedef conflict with SQL keyword

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

Shows a typedef named 'start' that conflicts with the SQL keyword START, causing a syntax error in EXEC SQL START TRANSACTION. This example is provided to illustrate the restriction.

```c
EXEC SQL BEGIN DECLARE SECTION;
    typedef int start;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL START TRANSACTION;
```

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

### Run tests against an existing installation

Source: https://www.postgresql.org/docs/19/regress-run.html

After installation, initialize a data directory and start the server, then run 'make installcheck' to execute the tests. The tests contact the server at the local host and default port unless PGHOST and PGPORT are set. They run in a database named 'regression', which is dropped if it exists.

```shell
make installcheck
```

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

### Install file_fdw extension

Source: https://www.postgresql.org/docs/19/file-fdw.html

Run this SQL command to install the file_fdw extension. This is the first step in the example for creating a foreign table for PostgreSQL CSV logs.

```sql
CREATE EXTENSION file_fdw;
```

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

### Install world with make install-world

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

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

```shell
make install-world
```

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

### Short Version Installation Commands

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

Quick installation commands for PostgreSQL using Autoconf and Make. Run as root for make install, then create a postgres user and initialize the 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
```

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

### Examples of numeric constants with underscores

Source: https://www.postgresql.org/docs/19/sql-syntax-lexical.html

Examples of numeric constants with underscores for visual grouping. Underscores have no effect on the value and are not allowed at the start or end of a constant or a group of digits.

```text
1\_500\_000\_000 
0b10001000\_00000000 
0o\_1\_755 
0xFFFF\_FFFF 
1.618\_034
```

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

### spi_exec_prepared with limit attribute example

Source: https://www.postgresql.org/docs/19/plperl-builtins.html

Example of using the optional limit attribute in spi_exec_prepared to limit the number of rows returned. Shows setup and execution of a prepared query.

```perl
CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address
 FROM generate_series(1,3) AS id;

CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$
 $_SHARED{plan} = spi_prepare('SELECT * FROM hosts
 WHERE address << $1', 'inet');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$
 return spi_exec_prepared(
 $_SHARED{plan},
 {limit => 2},
 $_[0]
 )->{rows};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$
 spi_freeplan($_SHARED{plan});
 undef $_SHARED{plan};
$$ LANGUAGE plperl;

SELECT init_hosts_query();
SELECT query_hosts('192.168.1.0/30');
SELECT release_hosts_query();

 query_hosts
-----------------
 (1,192.168.1.1)
 (2,192.168.1.2)
(2 rows)
```

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

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

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

Installs the world build when documentation was not built.

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

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

### Examples of current date/time functions

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

Shows example outputs for CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(2), and LOCALTIMESTAMP. These functions return the transaction start time 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
```

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

### Start upgraded publisher server on node1

Source: https://www.postgresql.org/docs/19/logical-replication-upgrade.html

Starts the upgraded publisher server on node1 with logging.

```bash
pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
```

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

### Initial meson setup command

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

Creates and configures the build directory. The builddir is mandatory; srcdir is deduced from the current directory and meson.build location.

```shell
meson setup build
```

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

### Example table of contents listing

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

Shows the format of the listing file generated by pg_restore -l. 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
```

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

### regexp_count syntax and examples

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

Syntax for regexp_count(string, pattern [, start [, flags ]]) → integer. Examples show counting matches, with the second using start=1 and flag 'i' for case-insensitive matching.

```sql
regexp_count(string, pattern [, start [, flags ]]) → integer
```

```sql
regexp_count('ABCABCAXYaxy', 'A.')          3
regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  4
```
Tokens:
Raw