psql
Connect
psql -h myhostname -U myuser mydb
Flags
-c 'command'
-E
-f myfile
-t
-v var="value"
-v var='value'
--command='...'
--csv
--echo-hidden
--file=myfile
e.g. pgsql -c 'SELECT * FROM mytable;'
Output to csv
Show SQL of metacommands
Run query from a file
Assign a value to a variable called var (reference inside psql using :var)
As above. You can do this without quotes but this risks SQL injection. (1)
7.1
12
7.1
7.1
TODO: Complete list
Meta Commands
\!
\?
\a
\c dbname
\cd
\conninfo
\copy
\d
\db
\dt
\dv
\dm
\di
\dn
\dT
\det
\e
\echo
\g myfile
\h
\H
\i file
\l
\o myfile
\q
\s
\s myfile
\set
\set var value
\sf funcname
\sv viewname
\timing
\unset var
\w myfile
\watch 9
\x
\connect dbname
\d+
\db+
\dt+
\dv+
\dm+
\di+
\dn+
\dT+
\det+
\h command
\html
\l+
8.4
7.1
7.1
7.1
7.1
Run host command
List all available meta commands
Toggle between unaligned and aligned output mode
Connect to database dbname. Also: \connect dbname dbuser dbhost dbport
Change directory that psql is working in
Display information about current connection
Copies to a file
Display tables, views and sequences
Display tablespaces
Display tables
Display views
Display materialized views
Display indexes
Display schemas
Display data types
Foreign Tables
Opens editor with last SQL statement
Print text or output of command enclosed in ` to the console
Run SQL in buffer and output to myfile
Help
Format output as HTML
Execute commands from myfile
List databases
Write output to myfile (instead of console)
Quit psql
Show history
Save history to myfile
List all internal variables
Set internal variable var to value
Show Function definition for funcname
Show View definition for viewname
Toggles timing on queries
Unset internal variable var
Save query buffer to myfile
Execute query every 9 seconds
Toggle expanded display
TODO: Complete list
Variable Handling
Passing variables on the command line...
psql -h ${HOST} -U ${USER} -d $DB -t -v myvar='${MYVAR}'
Formatting
~/.psqlrc
Allows you to set defaults that will be set each time you launch psql
\pset format wrapped
\pset columns 100
\pset columns 0
\pset linestyle unicode
\pset pager off
\H
\x
\x on
\x off
\x auto
Toggle HTML
Toggle Expanded mode
Expanded mode on
Expanded mode off
Expanded mode auto
Pagers & Editors
PAGER="less -S"Â
set PSQL_EDITOR=vi
Use \e to open last SQL statement in the editor you have setBibliography & References
https://psql-tips.org/psql_tips_all.htmlhttps://psql-tips.org/psql_tips_142.htmlhttps://dataschool.com/learn-sql/meta-commands-in-psql/https://stackoverflow.com/questions/3949876/how-to-switch-databases-in-psqlhttps://stackoverflow.com/questions/50737662/psql-alternatives-to-sqlplus-formatting-options
Variable Handling(1) https://www.depesz.com/2023/05/28/variables-in-psql-how-to-use-them/https://stackoverflow.com/questions/42119147/how-can-i-ask-user-to-enter-the-input-in-postgressql-plpgsql
Formattinghttps://dba.stackexchange.com/questions/1728/how-to-wrap-long-lines-when-selecting-sql-text-columnshttps://stackoverflow.com/questions/9604723/alternate-output-format-for-psql-showing-one-column-per-line-with-column-namehttps://github.com/okbob/pspg (pspg) - alternate pagerhttps://learnersguide.wordpress.com/2015/11/25/psql-horizontal-table-display/