Comprehensive Guide to Common psql Commands
The psql tool is PostgreSQL's command-line interface, allowing users to interact with their databases through commands and queries. This guide provides an overview of commonly used psql commands, their syntax, examples, and explanations.
Basic Syntax
The general syntax to invoke psql is as follows:
psql [options] [database_name] [user_name]
Example:
psql -U postgres -d mydb
Explanation:
Connecting to PostgreSQL using psql
# Connect to a specific database psql -U username -d database_name
Example:
psql -U admin -d employees_db
Common psql Commands
1. Check Current Database
\conninfo
Explanation: Displays the current database connection details.
2. List Databases
\l
Explanation: Lists all databases in the PostgreSQL instance.
3. Connect to Another Database
\c database_name
Example:
\c sales_db
Explanation: Switches to the sales_db database.
4. List Tables
\dt
Explanation: Lists all tables in the current database.
5. Describe a Table
\d table_name
Example:
\d employees
Explanation: Displays the schema, indexes, and constraints of the employees table.
6. Execute SQL Queries
SELECT * FROM table_name;
Example:
Code:
SELECT * FROM employees WHERE department = 'HR';
Explanation: Executes an SQL query to retrieve all employees in the HR department.
7. View Query History
\s
Explanation: Displays the history of commands executed in the current session.
8. Export Query Results to a File
\o filename
Example:
Code:
\o query_results.txt
SELECT * FROM employees;
\o
Explanation: Exports the query results to query_results.txt and stops the export with \o.
9. Quit psql
\q
Explanation: Exits the psql session.
Advanced psql Commands:
1. Set Output Format
\x
Explanation: Toggles between expanded and standard output formats.
2. List all Users
\du
Explanation: Displays all roles (users) in the PostgreSQL instance.
3. View Server Settings
SHOW setting_name;
Example:
Code:
SHOW timezone;
Explanation: Displays the server's current timezone setting.
Examples in Practice
Code:
# Start a new session
psql -U postgres
# List all databases
\l
# Connect to the "company" database
\c company
# List tables in the current database
\dt
# Describe the "departments" table
\d departments
# Execute a SQL query
SELECT * FROM departments;
# Quit the session
\q
Explanation of Key Commands:
1. \dt: Lists all tables in the connected database.
2. \d table_name: Provides detailed information about a specific table, including its schema and constraints.
3. SELECT: Used to query data from tables based on conditions.
4. \o filename: Redirects query results to a file for easy sharing or analysis.
Best Practices:
1. Session History: Use \s to view past commands for easy debugging or repetition.
2. Export Data: Redirect query results using \o for better reporting.
3. Explore Metadata: Leverage commands like \dt and \du to navigate the database structure.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4