cloudnative wiki

psql

2 min read

psql

Sure, here is a comprehensive cheat sheet for the PostgreSQL command-line interface (psql). This should cover most common scenarios you might encounter while working with PostgreSQL.

Connecting to PostgreSQL

  • Connect to a database

    psql -h hostname -p port -U username -d database

  • Connect to a database with a URL

    psql postgresql://username:password@hostname:port/database

  • Switch to a different database

    \c database_name

psql Meta-Commands

  • List all databases

    \l or \list

  • List all tables in the current database

    \dt

  • Describe a table

    \d table_name

  • List all indexes

    \di

  • List all sequences

    \ds

  • List all views

    \dv

  • List all functions

    \df

  • List all roles

    \du

  • List current connections

    \conninfo

  • List all schemas

    \dn

  • Show current user

    \conninfo

SQL Commands

  • Create a new database

    CREATE DATABASE database_name;

  • Drop a database

    DROP DATABASE database_name;

  • Create a new table

    CREATE TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
);

  • Drop a table

    DROP TABLE table_name;

  • Insert data into a table

    INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

  • Update data in a table

    UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

  • Delete data from a table

    DELETE FROM table_name
WHERE condition;

  • Select data from a table

    SELECT column1, column2, ...
FROM table_name
WHERE condition;

User and Role Management

  • Create a new user

    CREATE USER username WITH PASSWORD 'password';

  • Drop a user

    DROP USER username;

  • Grant privileges

    GRANT privilege ON object TO user;

  • Revoke privileges

    REVOKE privilege ON object FROM user;

Backup and Restore

  • Backup a database

    pg_dump -h hostname -U username -d database -F c -b -v -f backup_file

  • Restore a database

    pg_restore -h hostname -U username -d database -v backup_file

Transactions

  • Start a transaction

    BEGIN;

  • Commit a transaction

    COMMIT;

  • Rollback a transaction

    ROLLBACK;

System Information

  • Show server version

    SELECT version();

  • Show current database

    SELECT current_database();

  • Show current user

    SELECT current_user;

  • Show current date and time

    SELECT now();

Meta-Command Shortcuts

  • Quit psql

    \q

  • Clear the screen

    \! clear

  • Execute shell command

    \! command

  • Get help on SQL commands

    \h or \help

  • Get help on psql commands

    \?

Miscellaneous

  • Enable timing of queries

    \timing

  • Show query execution plan

    EXPLAIN query;

  • Show detailed query execution plan

    EXPLAIN ANALYZE query;

  • Set environment variable

    export PGUSER=username
export PGPASSWORD=password

This cheat sheet should help you navigate and use psql effectively for most scenarios you might encounter while working with PostgreSQL.

Graph View