Useful PostgreSQL commands for command line

Certainly! Here are some useful PostgreSQL commands that you can use via the command line interface (CLI), specifically through the psql utility. These commands help you interact with your PostgreSQL database for various purposes, from administrative tasks to data manipulation:

Connecting to a PostgreSQL Database:

• Connect to a PostgreSQL database:

psql -d database_name -U user_name

• Connect to a PostgreSQL database on a specific host and port:

psql -h host_name -p port_number -d database_name -U user_name

Listing Databases and Tables:

• List all databases: \l or \list
• List all tables in the current database: \dt (for tables in the public schema) or \dt *.* (for tables in all schemas)

Switching Databases:

• Switch connection to another database: \c database_name

Viewing Table Structure:

• Describe a table’s structure (columns, types, etc.): \d table_name

Executing SQL Queries:

• Execute an SQL query directly from the command line:

psql -d database_name -U user_name -c "SELECT * FROM table_name;"

Exporting and Importing Data:

• Export data to a file:

psql -d database_name -U user_name -c "COPY table_name TO '/path/to/file.csv' DELIMITER ',' CSV HEADER;"

• Import data from a file:

psql -d database_name -U user_name -c "\COPY table_name FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;"

Managing Users and Permissions:

• Create a new user: CREATE USER user_name WITH PASSWORD 'password';
• Grant privileges to a user on a database: GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;
• Revoke privileges from a user on a database: REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name;

Database Maintenance:

• Vacuum a database (clean up and optimize): VACUUM (VERBOSE, ANALYZE) table_name;
• Reindex a database: REINDEX DATABASE database_name;
• Show running queries: SELECT * FROM pg_stat_activity;
• Cancel a running query: SELECT pg_cancel_backend(pid);

Exiting psql:

• Exit the psql utility: \q

These commands cover a broad range of operations you might need to perform when managing PostgreSQL databases from the command line. Remember to replace placeholders like database_name, user_name, table_name, and file paths with your actual database names, user names, table names, and file paths.