psql

Connect to the database. By default, it connects to the local socket using port 5432 with the currently logged in user

$ psql [database]

$ psql [[-h|--host]] [host] [[-p|--port]] [port] [[-U|--username]] [username] [database]

$ psql [[-h|--host]] [host] [[-p|--port]] [port] [[-U|--username]] [username] [[-W|--password]] [database]

$ psql [[-c|--command]] '[query]' [database]

$ psql [database] [[-f|--file]] [file.sql]

psql [OPTIONS] [DBNAME [USERNAME]]
psql [OPTIONS] [CONNECTION_STRING]

-h hostname
    Specifies the database server's hostname or IP address to connect to.

-p port
    Specifies the TCP/IP port or local Unix domain socket file extension on which the server is listening for connections.

-U username
    Connects to the database as the specified username.

-d dbname
    Specifies the name of the database to connect to. Defaults to the user's username if not provided.

-c command
    Executes a single SQL command string and exits. Useful for quick queries or scripting.

-f filename
    Executes SQL commands from the specified filename and exits. Ideal for running scripts or batch operations.

-w
    Never prompts for a password. If the server requires password authentication and a password is not available from other means (e.g., .pgpass), the connection attempt will fail.

-W
    Forces psql to prompt for a password before connecting to a database, even if a .pgpass file or other method would provide one.

-l
    Lists all available databases on the connected server and then exits. Similar to the interactive \l meta-command.

-V, --version
    Prints the psql version and exits.

-?, --help
    Shows help about psql command line arguments and exits.

-q
    Runs in quiet mode. Suppresses startup messages and reduces verbosity during execution.

-t
    Prints only tuples (rows), without column names, footers, or other extraneous output, making it useful for scripting output parsing.

psql is the foundational command-line interface for interacting with PostgreSQL databases, widely recognized as a leading open-source relational database management system.
It offers a powerful and flexible environment for users to connect to database instances, execute standard SQL queries, and perform comprehensive database administration and data management operations.
Functioning both interactively, where it provides a rich set of meta-commands (prefixed with \) for tasks like inspecting tables (\d) or listing databases (\l), and non-interactively, allowing for automated script execution (via -f option) or single command processing (with -c option), psql adapts to various workflows.
It is an essential tool for PostgreSQL developers, database administrators, and data analysts, providing a direct and efficient way to manage database objects, manipulate data, inspect schema definitions, and streamline routine tasks.

Connecting to a PostgreSQL server requires it to be running and accessible over the network or via a local socket.
For secure password handling, it is highly recommended to utilize the .pgpass file or environment variables (e.g., PGPASSWORD) rather than embedding passwords directly in scripts or command lines.
psql's powerful meta-commands (those starting with \) are specific to psql and are not standard SQL syntax, meaning they cannot be executed by other SQL clients.

When run without the -c or -f options, psql enters an interactive mode. In this mode, users can type SQL commands or meta-commands. The meta-commands (prefixed with a backslash \) are specific psql commands designed for administrative tasks and schema exploration. Examples include \d (describe table/view), \dt (list tables), \l (list databases), \q (quit), \e (edit current query buffer in an external editor), and \? (list all available meta-commands with short descriptions).

Beyond its interactive capabilities, psql is a powerful tool for scripting and automation. The -f option allows for the execution of an entire SQL script file, which is invaluable for database migrations, bulk data loading, or applying schema changes.
Similarly, the -c option enables the execution of a single SQL command directly from the command line, facilitating quick programmatic interactions with the database without entering an interactive session.

psql's behavior and appearance can be extensively customized by creating a startup file named .psqlrc in the user's home directory. This file can contain any psql commands, including \set to define custom variables, \pset to control output formatting (e.g., table borders, titles), or even SQL commands to be executed upon every psql session start. This allows users to tailor their environment for improved efficiency and readability.

psql has been an integral part of the PostgreSQL project since its inception, tracing its roots back to the Postgres project at the University of California, Berkeley.
Over the decades, it has continuously evolved alongside the PostgreSQL server, incorporating new features, improving user experience, and adapting to modern database administration needs, solidifying its role as the primary command-line interface for the database.

pg_dump(1), pg_restore(1), createdb(1), dropdb(1), pg_ctl(1)