September 12, 2025

Quick Reference Guide to PostgreSQL Commands

A quick reference guide to essential PostgreSQL commands. Learn how to list databases, users, tables, schemas, and check environment details directly from the psql shell.

PostgreSQL is a powerful open-source relational database system. If you spend much time inside the psql interactive shell, you’ll want a handy reference to the most commonly used commands. This guide provides a quick lookup for listing databases, users, tables, and more.

All examples below assume you are inside the psql prompt, unless noted otherwise.

Connecting and Exiting

Before you can explore your PostgreSQL environment, you need to connect:

psql -U username -d database

1 2	psql -U username -d database

This command connects you to the database using the specified username. Once you’re finished, you can quit out of psql with:

\q

Working with Databases

PostgreSQL can host multiple databases within the same cluster. These commands help you view and switch between them:

\l                -- List all databases
\c dbname         -- Connect to a different database
SELECT current_database();  -- Show the current database

\l -- List all databases

\c dbname -- Connect to a different database

SELECT current_database(); -- Show the current database

Listing Users and Roles

Roles in PostgreSQL can act as both users and groups. To see who exists in your system:

\du               -- List all users and roles
\dg               -- Same as \du
SELECT usename FROM pg_user;  -- Query all users

\du -- List all users and roles

\dg -- Same as \du

SELECT usename FROM pg_user; -- Query all users

Exploring Tables and Schemas

Schemas provide logical separation of tables and other objects. To explore what’s available:

\dt               -- List tables in the current schema
\dt *.*           -- List tables across all schemas
\d tablename      -- Show table structure
\dn               -- List all schemas

\dt -- List tables in the current schema

\dt *.* -- List tables across all schemas

\d tablename -- Show table structure

\dn -- List all schemas

Checking Connection and Environment Info

These commands provide information about your session and environment:

\conninfo         -- Show current connection info
\encoding         -- Display client encoding
\password         -- Change password for the current user

\conninfo -- Show current connection info

\encoding -- Display client encoding

\password -- Change password for the current user

Useful Queries

A few SQL queries you’ll often find helpful:

SELECT version();     -- PostgreSQL version
SELECT current_user;  -- Show current user
SELECT now();         -- Current timestamp

SELECT version(); -- PostgreSQL version

SELECT current_user; -- Show current user

SELECT now(); -- Current timestamp

Administrative Queries

To get a quick overview of system-level information:

SELECT datname FROM pg_database;  -- List all databases
SELECT rolname FROM pg_roles;     -- List all roles

SELECT datname FROM pg_database; -- List all databases

SELECT rolname FROM pg_roles; -- List all roles

Conclusion

These commands cover the basics you’ll use most often in PostgreSQL. Whether you’re a developer, database administrator, or just getting started, having a quick reference can save you time and reduce frustration.

If you need help with PostgreSQL hosting, administration, or performance tuning, the Reliable Penguin team can assist. We provide managed hosting and expert support to keep your databases running smoothly.