Moving around in PostgreSQL with the CLI

psql-cli.md

Here’s a quick guide to basic navigation inside a PostgreSQL database using the command-line interface (CLI).

1. Access PostgreSQL To start using PostgreSQL, open a terminal and enter:

psql -U your_username -d your_database

Replace your_username with your PostgreSQL username and your_database with the name of the database you want to access.

If you're connecting as the default PostgreSQL user (postgres), use:

psql -U postgres

If PostgreSQL requires a password, enter it when prompted.

2. List Databases Once inside the PostgreSQL shell, list all databases with:

\l

or:

\list

3. Switch to a Different Database To connect to another database:

\c database_name

or:

\connect database_name

4. List Tables in the Current Database To see all tables in the database:

\dt

For more details about tables:

\dt+

5. Describe a Table Structure To see the structure of a specific table:

\d table_name

For extended details:

\d+ table_name

6. List Schemas To view all schemas in the database:

\dn

7. List Users (Roles) To see all users (roles) in the PostgreSQL instance:

\du

8. Run a Query Execute SQL commands, such as selecting all rows from a table:

SELECT * FROM table_name;

9. Exit the PostgreSQL Shell To leave the psql prompt, type:

\q