webinstall.dev/psql Star on GitHub

psql: REPL for Postgres that supports standard TLS (+SNI,ALPN), .pgpass, and .psqlrc

https://www.postgresql.org/| Installer Source| Releases (json) (tab)

To update or switch versions, run webi psql@stable (or @v17.0, @beta, etc).

Files

These are the files / directories that are created and/or modified with this install:

~/.config/envman/PATH.env
~/.local/opt/psql/
~/.pgpass
~/.psqlrc
~/.config/psql/psqlrc.sql

Cheat Sheet

psql serves as a model for what a great PostgreSQL Client should be:

  • gives you a nice REPL to run queries
  • manages authentication
  • allows table or list view
  • preloads user-specific settings

For localhost / private networking:

psql "postgres://postgres:postgres@localhost:5432/postgres"
psql "postgres://db-xxxx:secret123@pg-1.example.com:5432/db-xxxx"

For remote / public networks:
(with sslmode & sslnegotiation)

psql "postgres://db-xxxx@pg-1.example.com:5432/db-xxxx?sslmode=require&sslnegotiation=direct"

Table of Contents

  • Server vs Client & PG Essentials
  • Vertical Rows
  • .pgpass
  • .psqlrc
  • How to Import / Export CSV
  • How to Backup & Restore
  • How to Create a Table
  • Session Variables (& Encryption Keys)

Where to Find the Postgres Server Cheat Sheet

This is exclusively a client-side cheat sheet.

For the server-side / administrative cheat sheet, see The Postgres (Server) Cheat Sheet.

For a collection of other helpful scripts, see PG Essentials:

  • psql-example-connect
  • psql-backup
  • psql-store-credential

How to View Rows Vertically

  • use \gx instead of ; for a single query
  • use \x to toggle for all queries
SELECT id, character_name, description
FROM character_descriptions \gx

-[ RECORD 1 ]--+------------------------------------------------------------------------------------------------------------------
id             | e17709d3-6e5a-4baa-91cc-8d5267815d5e
character_name | Harry Potter
description    | A teenager with a chronic case of "Chosen One" syndrome, who manages to stay alive by sheer luck.
-[ RECORD 2 ]--+------------------------------------------------------------------------------------------------------------------
id             | 68646acf-9468-4006-83a7-8ee748df4ca5
character_name | Spider-Man
description    | The neighborhood's most responsible guy, who balances high school stress with the casual task of saving the city.
  • similar to \G in MySQL.

How to use ~/.pgpass for Passwords

Postgres passwords are stored in ~/.pgpass.

You can use psql-store-credential to manage ~/.pgpass, or manage it manually:

touch ~/.pgpass
chmod 0600 ~/.pgpass

# export PGPASSFILE='/Users/aj/.pgpass'
# hostname:port:database:username:password
localhost:5432:postgres:postgres:postgres
pg-1.example.com:5432:db-xxxx:secret123
localhost:*:*:postgres:postgres
*:*:db-xxxx:db-xxxx:secret123
*:*:*:postgres
  • this ONLY supplies a password - not a default username or db name
  • the Database Name and User Name are typically the same
    (as per samename in hba.conf)
  • all but the password can * wildcards
  • the first line to match (NOT the most specific) will used for password

How to setup ~/.psqlrc for Per-DB history

Allows you to keep per-database history and settings, such as encryption keys.

mkdir -p ~/.config/psql/

touch ~/.psqlrc
touch ~/.config/psql/psqlrc.sql

chmod 0600 ~/.psqlrc
chmod 0700 ~/.config ~/.config/psql/
chmod 0600 ~/.config/psql/psqlrc.sql

~/.psqlrc

\i ~/.config/psql/psqlrc.sql

~/.config/psql/psqlrc.sql:

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

--
-- Per-DB Configuration
--
\set HISTFILE ~/.config/psql/ :DBNAME /history
\set confdir ~/.config/psql/ :DBNAME
\set dbrc :confdir /psqlrc.sql
\if `mkdir -p :confdir && chmod 0700 :confdir && echo n || echo y`
    \echo [WARN] could not create :confdir
    \set HISTFILE ~/.config/psql/history
\else
    \if `test -f :dbrc || touch :dbrc && chmod 0600 :dbrc && echo n || echo y`
        \echo [WARN] could not create :dbrc
    \else
        \echo loading :dbrc
        \i :dbrc
    \endif
    \if `test -f :HISTFILE || touch :HISTFILE && chmod 0600 :HISTFILE && echo n || echo y`
        \echo [WARN] could not create :HISTFILE
        \set HISTFILE ~/.config/psql/history
    \endif
\endif
\unset :dbrc

--
-- Session Preferences
--
-- ignore space-prefixed commands and duplicates
\echo using :HISTFILE for command history

\set QUIET on
\set HISTCONTROL ignoreboth
\set ON_ERROR_ROLLBACK interactive
\set COMP_KEYWORD_CASE upper
\pset pager off
\pset null '(null)'

-- set to YOUR timezone
SET TIME ZONE 'America/Denver';

\unset QUIET

\echo ''
  • HISTCONTROL ignoreboth causes lines starting with a space and duplicate lines to be omitted from history

How to Work with CSVs

  • \copy saves client-side (locally, relative to psql)
    • MUST be on a SINGLE LINE (no newlines)
    • (use a temporary view for for queries that don't easily fit on a line)
  • COPY saves server-side (remote, relative to postgres)

How to Export to CSV

\copy "character_descriptions" TO './character_descriptions.csv' WITH CSV HEADER;

CREATE TEMP VIEW "character_descriptions_csv" AS
SELECT "character_name", "description"
FROM "character_descriptions";

\copy (SELECT * FROM "character_descriptions_csv") TO './character_descriptions.csv' WITH CSV HEADER;

How to Import from CSV

\copy "character_descriptions"("id", "character_name", "description") FROM './character_descriptions.csv' WITH (FORMAT csv, HEADER);

How to Backup & Restore

To backup in a way that will be easy to restore:

  • save the schema separately from the data
  • don't include database-specific roles or permissions
  • store the password in ~/.pgpass as described above

You can use a helper script like psql-backup, or create your own:

Given these credentials:

my_user="db_xxxx"
my_db="db_xxxx"
my_host="pg-1.example.com"
my_port="5432"

And these pg_dump commands:

pg_dump --no-privileges --no-owner --schema-only --clean \
    --username "$my_user" --no-password --host "$my_host" --port "$my_port" \
    -f ./"$my_db".schema.drop.sql "$my_db" >&2

pg_dump --no-privileges --no-owner --schema-only \
    --username "$my_user" --no-password --host "$my_host" --port "$my_port" \
    -f ./"$my_db".schema.sql "$my_db" >&2

pg_dump --no-privileges --no-owner --data-only \
    --username "$my_user" --no-password --host "$my_host" --port "$my_port" \
    -f ./"$my_db".data.sql "$my_db"

You'll get your data is this format:

db_xxxx.schema.drop.sql # will replace (DELETE) all tables with empty tables
db_xxxx.schema.sql      # will create new empty tables
db_xxxx.data.sql        # will load data

To restore / copy to another database:

new_user="db_yyyy"
new_db="db_yyyy"
psql "postgres://$new_user@$my_host:$my_port/$new_db" < ./db_xxxx.schema.sql
psql "postgres://$new_user@$my_host:$my_port/$new_db" < ./db_xxxx.data.sql

Or use pg_restore.

See the examples at:

How to Create a Table

CREATE TABLE "example_table" (
    -- Primary Key using gen_random_uuid()
    "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Numeric types
    "small_int_column" SMALLINT,
    "positive_integer_column" INTEGER CHECK ("positive_integer_column" > 0),
    "big_int_column" BIGINT,
    "decimal_column" DECIMAL(10, 2),
    "numeric_column" NUMERIC(15, 3),
    "real_column" REAL,
    "double_precision_column" DOUBLE PRECISION,
    "serial_column" SERIAL NOT NULL UNIQUE,
    "bigserial_column" BIGSERIAL NOT NULL UNIQUE,

    -- Monetary type
    "money_column" MONEY,

    -- Character types
    "char_column" CHAR(10),
    "varchar_column" VARCHAR(255),
    "text_column" TEXT,

    -- Binary types
    "bytea_column" BYTEA,

    -- Date/time types
    "timestamp_column" TIMESTAMP,
    "timestamp_tz_column" TIMESTAMP WITH TIME ZONE,
    "date_column" DATE,
    "time_column" TIME,
    "time_tz_column" TIME WITH TIME ZONE,
    "interval_column" INTERVAL,

    -- Boolean type
    "boolean_column" BOOLEAN,

    -- UUID type
    "uuid_column" UUID,

    -- JSON types
    "json_column" JSON DEFAULT '{}',
    "jsonb_column" JSONB,

    -- Array types
    "int_array_column" INTEGER[],
    "text_array_column" TEXT[],

    -- Geometric types
    "point_column" POINT,
    "line_column" LINE,
    "lseg_column" LSEG,
    "box_column" BOX,
    "path_column" PATH,
    "polygon_column" POLYGON,
    "circle_column" CIRCLE,

    -- Network address types
    "cidr_column" CIDR,
    "inet_column" INET,
    "macaddr_column" MACADDR,
    "macaddr8_column" MACADDR8,

    -- Bit string types
    "bit_column" BIT(8),
    "varbit_column" BIT VARYING(8),

    -- Text search types
    "tsvector_column" TSVECTOR,
    "tsquery_column" TSQUERY,

    -- XML type
    "xml_column" XML,

    -- Range types
    "int4range_column" INT4RANGE,
    "numrange_column" NUMRANGE,
    "tsrange_column" TSRANGE,
    "tstzrange_column" TSTZRANGE,
    "daterange_column" DATERANGE,

    -- Hstore type
    "hstore_column" HSTORE,

    -- Unique constraint (example)
    CONSTRAINT "unique_example_table_columns" UNIQUE ("positive_integer_column", "varchar_column")
);

COMMENT ON COLUMN "example_table"."id" IS 'Unique identifier for the example table';

How to use Session Variables

Given the example psqlrc above which creates per-db history and config files, you can create a config file with the session variables you'd like to use in queries:

~/.config/psql/db_xxxx/psqlrc.sql:

-- ex: add conventional "my" extension with client params for pgp and raw encryption
SET SESSION "my"."client_id" = '12345678';
-- note: MUST be cast to ::bytea explicitly
--       SELECT current_setting('my.aes_128_key')::bytea;
SET SESSION "my"."aes_128_key" = E'\\xdeadbeefbadc0ffee0ddf00dcafebabe';
SET SESSION "my"."pgp_password" = 'zoo zoo zoo zoo zoo zoo zoo zoo zoo zoo zoo right';

SELECT current_setting('my.aes_128_key')::bytea;
SELECT current_setting('my.pgp_password');
  • Bound to the CURRENT LOGIN SESSION only
  • Can be used as a VALUE, anywhere - such as in FUNCTIONS and VIEWS
  • Must must be scoped "extension"."var", where "extension" is arbitrary
    (conventionally "my", but can be anything that isn't already a schema)
  • Can also be used programmatically by clients in an exec() or query()

Example Raw AES Encryption

This shows a full encrypt and decrypt example, using current_setting() to get a key.

-- raw encrypt / decrypt example
WITH "aes_key_cte" AS (
    SELECT
        current_setting('my.aes_128_key')::bytea AS "aes_key",
        'sensitive data (raw)' AS "plain_original"
),
"raw_example_table_cte" AS (
    SELECT
        "aes_key",
        "plain_original",
        encrypt(convert_to("plain_original", 'UTF8'), "aes_key", 'aes') AS "raw_enc_column"
    FROM
        "aes_key_cte"
)
SELECT
    "plain_original",
    "raw_enc_column",
    convert_from(decrypt("raw_enc_column", "aes_key", 'aes'), 'UTF8') AS "plain_decrypted",
    convert_from(decrypt_iv(
        "raw_enc_column", "aes_key", E'\\x00000000000000000000000000000000', 'aes'),
    'UTF8') AS "plain_decrypted_iv"
FROM
    "raw_example_table_cte"
;

In practice, using a simple and efficient SQL functions can help abstract away the tedious bits.

See:

Example PGP AES Encryption

-- pgp_sym encrypt / decrypt example
WITH "pgp_pass_cte" AS (
    SELECT
        'sensitive data (pgp)' AS "plain_original",
        current_setting('my.pgp_password') AS "pgp_pass"
),
"pgp_example_table_cte" AS (
    SELECT
        -- pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
        -- pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
        pgp_sym_encrypt(
            'sensitive data (pgp)',
            "pgp_pass",
            'cipher-algo=aes128, unicode-mode=1'
        ) AS "pgp_enc_column"
    FROM
        "pgp_pass_cte"
)
SELECT
    "plain_original",
    "pgp_enc_column",
    -- pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
    -- pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea
    pgp_sym_decrypt(
        "pgp_enc_column",
        "pgp_pass",
        'cipher-algo=aes128, unicode-mode=1'
    ) AS "plain_decrypted"
FROM
    "pgp_example_table_cte"
    CROSS JOIN "pgp_pass_cte"

See:

Contribute

Report an Issue Submit Installer Star on GitHub