psql Command in Linux
The psql command in Linux is used to interact with PostgreSQL databases. It is a terminal-based front-end to PostgreSQL for executing queries interactively, running scripts from files, and viewing results. It includes meta-commands and shell-like features for scripting and automation.
Table of Contents
Here is a comprehensive guide to the options available with the psql command â
- Installation of psql Command in Linux
- Syntax of psql Command
- psql Command Options
- Examples of psql Command in Linux
Installation of psql Command in Linux
The psql command requires PostgreSQL installed on Linux. To install PostgreSQL on Ubuntu, Kali Linux, Raspberry Pi OS, Debian, and other Debian-based distributions, use the following command −
sudo apt install postgresql
To install it on CentOS, use the following command −
sudo yum install postgresql
To install PostgreSQL on Fedora, use the command given below −
sudo dnf install postgresql
To verify the installation of psql command in Linux, check its version −
psql --version
Before using the psql command, the postgresql service must be enabled and start −
sudo systemctl start postgresql sudo systemctl enable postgresql
To view the status of the PostgreSQL service, use −
sudo systemctl status postgresql
Syntax of psql Command
The syntax of the psql command in Linux is as follows −
psql [options] [dbname] [username]
The [options] field is used to specify various options. The [dbname] is the name of the database to connect to, and [username] is the PostgreSQL user for authentication.
psql Command Options
The options of the Linux psql command are listed below −
| Flag | Option | Description |
|---|---|---|
| -a | --echo-all | Print all input lines to standard output as they are read. Useful for script processing. |
| -A | --no-align | Switch to unaligned output mode. The default is aligned mode. |
| -c command | --command command | Execute a single command string and exit. Useful for shell scripts. |
| -d dbname | --dbname dbname | Specify the database name to connect to. |
| -e | --echo-queries | Print all SQL commands sent to the server. |
| -E | --echo-hidden | Print actual queries generated by \d and other backslash commands. |
| -f filename | --file filename | Execute commands from a file instead of interactive input. |
| -F separator | --field-separator separator | Use the separator as the field separator for unaligned output. |
| -h hostname | --host hostname | Specify the hostname of the machine running the server. |
| -H | --html | Enable HTML tabular output. |
| -l | --list | List all available databases and exit. |
| -L filename | --log-file filename | Write all query output to filename in addition to normal output. |
| -n | --no-readline | Disable readline for line editing and history. |
| -o filename | --output filename | Redirect all query output to the filename. |
| -p port | --port port | Specify the TCP port or Unix-domain socket file extension for the server. |
| -P assignment | --pset assignment | Set printing options in the style of \pset. |
| -q | --quiet | Suppress welcome messages and informational output. |
| -R separator | --record-separator separator | Use the separator as the record separator for unaligned output. |
| -s | --single-step | Enable single-step mode, prompting before each command execution. |
| -S | --single-line | Enable single-line mode, where a newline terminates an SQL command. |
| -t | --tuples-only | Disable column names and row count footers. |
| -T table_options | --table-attr table_options | Set options for the HTML <table> tag. |
| -U username | --username username | Connect as the specified username instead of the default. |
| -v assignment | --set / --variable assignment | Assign a variable as in \set. |
| -V | --version | Print psql version and exit. |
| -w | --no-password | Never prompt for a password. |
| -W | --password | Force a password prompt before connecting. |
| -x | --expanded | Enable expanded table formatting mode. |
| -X | --no-psqlrc | Do not read startup files (psqlrc). |
| -1 | --single-transaction | Wraps script execution in a single transaction when used with -f. |
| -? | --help | Display help information and exit. |
Examples of psql Command in Linux
This section demonstrates the usage of the psql command in Linux with examples −
Note − The following examples are only for the same system user to log in without a password.
- Switching to the PostgreSQL System User
- Listing all Available Databases
- Connecting to a Database
- Executing a Query
- Disabling Column Names
- Displaying Output in HTML
- Enabling Single Step Mode
- Displaying Unaligned Output
- Displaying the Command Sent to the Server
- Storing the Query Output to a File
- Displaying the Table in Expanded Format
- Displaying Usage Help
Switching to the PostgreSQL System User
To switch to the PostgreSQL system user, use the following command −
sudo -i -u postgres
To start an interactive session, use the psql command −
psql
Note − This works if PostgreSQL is installed and the postgres system user exists. If authentication fails, the pg_hba.conf file may require modification to allow peer or password-based authentication.
To run the meta-commands in the psql session, use the slash (\). For example, to list all the databases, use \l, and to quit the session, use \q as shown in the following image −
A list of commands that can be used in the interactive session are listed below −
| Command | Description |
|---|---|
| \? | List all available meta-commands. |
| \q | Quit psql. |
| \c dbname | Connect to a different database. |
| \conninfo | Show current connection details. |
| \password | Change the password for the current user. |
| \l or \list | List all databases. |
| \dn | List all schemas. |
| \db | List available tablespaces. |
| \dt | List all tables. |
| \dt schema_name.* | List tables in a specific schema. |
| \d table_name | Describe table structure (columns, indexes, etc.). |
| \di | List indexes. |
| \dv | List views. |
| \dm | List materialized views. |
| \du | List all roles/users. |
| \dg | List all groups. |
| \timing | Toggle execution time display. |
| \echo text | Print text output. |
| \o filename | Redirect query output to a file. |
| \i filename | Execute commands from a file. |
| \watch seconds | Re-run the last query at specified intervals. |
| \x | Toggle expanded table formatting. |
| \pset format aligned | Set output format (aligned, unaligned, CSV, etc.). |
| \H | Enable HTML output mode. |
| \a | Toggle unaligned output mode. |
| \t | Toggle displaying column headers and row count. |
The following examples are executed using psql command in non-interactive mode.
Listing All Available Databases
To list all the databases, use the -l or --list option −
psql -l
Connecting to a Database
To connect to a database, use the -d or --dbname option −
psql -d mydb
Executing a Query
To execute a query, use the -c or --command option. For example, to list all entries from mydb database and users table, use the psql command in the following way −
psql -d mydb -c "SELECT * FROM users"
Disabling Column Names
The -t or --tuples-only option removes column headers and row count footers, displaying only data rows −
psql -t -d mydb -c "SELECT * FROM users"
Displaying Output in HTML
To display the output in HTML tabular format, use the -H or --html option −
psql -H -d mydb -c "SELECT * FROM users"
Enabling Single Step Mode
To enable the single-step mode, use the -s or --single-step option. It prompts before each step −
psql -s -d mydb -c "SELECT * FROM users"
Displaying Unaligned Output
By default, the psql outputs results in an aligned table format. To switch to unaligned output, use the -A or --no-align option.
psql -A -d mydb -c "SELECT * FROM users"
Displaying the Command Sent to the Server
To display the queries sent to the server, use the -e or --echo-queries option −
psql -e -d mydb -c "SELECT * FROM users"
Storing the Query Output to a File
To save the query output to a file, use the -L or --log-file option with the file name −
psql -L "log.txt" -d mydb -c "SELECT * FROM users"
The -L/--log-file option logs all query output and session activity, not just the result of a single command.
For verification, use the cat command.
cat log.txt
Note that the file will be generated in /var/lib/postgresql directory.
Displaying the Table in Expanded Format
To display the table in expanded output, use the -x or --expanded option −
psql -x -d mydb -c "SELECT * FROM users"
Displaying Usage Help
To display the usage help of the psql command, use the -? or --help option −
psql -?
Conclusion
The psql command in Linux is a powerful tool for interacting with PostgreSQL databases from the terminal. It allows executing queries, running scripts, and managing databases with various options for formatting and automation. Installation varies by distribution, and numerous command-line options provide flexibility in database management. Common tasks include listing databases, connecting to a database, executing queries, modifying output formats, and saving query results.