# Databases : PostgreSQL : CLI ## Environment Variables Picked up and used by many PostgreSQL commands (or anything based on libpq). - PGHOST - PGPOST - PGDATABASE — defaults to "postgres" - PGUSER — defaults to system name - PGPASSWORD ## psql ``` psql [options] [dbname [username]] -d | --dbname <dbname> # defaults to system username -h | --host <hostname> -p | --port <port> -U | --username <username> # defaults to system username -W | --password # interactively prompts user -c | --command <command> # repeatable; ex: psql -c '\x' -c 'SELECT * FROM foo;' # also echo '...;' | psql -l | --list # print list of databases and exit -e | --echo-hidden # echo commands to stdout -s | --single-step # single-step mode; pauses before sending each statement (for debugging) ``` #### Meta-Commands Anything you enter in `psql` that begins with a backslash is a meta-command processed by `psql` itself. Meta-commands are often called slash or backslash commands. ``` \? # help for psql \h [command] # help for SQL \c | \connect <dbname> # reconnect to different database \q | \quit # exit ## Introspection \l # list databases \d # list relations (tables, views, materialized views, sequences, foreign tables) \dt [pattern] # list tables \d [pattern] # show matching relations Examples: \dt pal* # list all tables starting with "pal" (in all schemas) \dt public.pal* # list all tables starting with "pal" (in "public" schema) \d foo # show details for relation "foo" (in any schema) \d foo* # show details for relations starting with "foo" (in any schema) \d foo.bar* # show details for relations starting with "bar" (in "foo" schema) ``` #### Prompts ``` mydb=> # regular user mydb=># # superuser ```