psql cheatsheet

.sh

# psql Cheatsheet

# Connect to a specific database as a specific user
psql -d mydatabase -U myuser -h localhost -p 5432

# Connect using defaults 
psql

# List all psql meta-commands
\?

# Get help for a specific SQL command
\h SELECT
\h CREATE TABLE

# List all available databases on the server
\l

# List databases with more details
\l+

# Show current connection information
\conninfo

# Connect to a different database
\c other_database_name

# Connect to a different database as a different user
\c other_database_name other_user_name

# List schemas in the current database
\dn

# List tablesin the current schema search path
\dt

# List tables in a specific schema
\dt public.*
\dt my_schema.*

# List tables across all schemas
\dt *.*

# List tables with more details
\dt+

# List all relations in current search path
\d

# List relations matching a pattern
\d user*

# List views only
\dv

# List sequences only
\ds

# List indexes only
\di

# List foreign tables only
\dft

# List functions only
\df

# List role
\du

# Describe a specific table, view, sequence, or index 
\d table_name
\d view_name

# Describe a table with even more details
\d+ table_name

# Execute the previous command again
\g

# Edit the current/previous command in an external editor
\e

# Execute a shell command from within psql
\! ls -l

# Toggle expanded output format
\x

# Toggle query execution timing on/off
\timing

# Set psql variables
\set PROMPT1 '%n@%/%R:%> '

# Quit psql
\q

# Basic SQL

# Standard SQL commands work directly in psql:
# SELECT column1, column2 FROM your_table WHERE condition;
# INSERT INTO your_table (col1, col2) VALUES (val1, val2);
# UPDATE your_table SET col1 = new_val WHERE condition;
# DELETE FROM your_table WHERE condition;
# CREATE TABLE your_table (...);
# ALTER TABLE your_table ...;
# DROP TABLE your_table;
# BEGIN; COMMIT; ROLLBACK; -- Transaction control