| psql | Comment | pgbash |
psql DB UID
\c[onnect] [dbname|- [user]] |
Set Database and
User name. |
connect to DB user UID; |
| psql -a |
Echo all input
from script |
?? |
psql -A
\a |
Aligned/Unaligned
table output mode |
A{+|-}
(set OPTION_ALIGN=[ON|OFF];) |
| psql -c query |
Run only single query |
-- |
| psql -d dbname |
Specify database name
to connect to(default:admin) |
connect to dbname;
(set connection dbname;)
or
exec_sql -d dbname "SQL" |
| psql -e |
Echo queries sent
to backend |
E+
(set OPTION_ECHO=ON;) |
| psql -E |
Display queries that
internal commands generate |
E+
(set OPTION_ECHO=ON;) |
| psql -f |
Execute queries from file,
then exit |
execute SHELL SCRIPT filename |
psql -F string
\f string |
Set field separator
(default: "|") |
S+ 'string'
(set OPTION_SEPARATOR='string';) |
| psql -h host |
Specify database server
host |
connect to DB@host; |
psql -H
\H |
HTML table output mode |
H+
(set OPTION_HTML=ON;) |
psql -l
\l |
List available databases |
?l
(_list_databases) |
| psql -n |
Disable readline |
-- |
psql -o file
\g [file]
\o [file] |
Send query output to
filename (or |pipe) |
SQL; > file
SQL; > file &
SQL; | pipe |
| psql -p port |
Specify database server
port |
connect to DB@host:port; |
| psql -P var[=arg] |
Set printing option
'var' to 'arg' |
set option_name = value; |
| psql -q |
Run quietly (no messages,
only query output) |
Q+
(set OPTION_QUIET=ON;) |
| psql -R |
Set record separator
(default: newline) |
-- |
| psql -s |
Single step mode
(confirm each query) |
-- |
| psql -S |
Single line mode
(newline terminates query) |
-- |
| psql -t |
Print rows only |
T- or B-
(set OPTION_HEADER=OFF;)
(set OPTION_BOTTOM=OFF;) |
psql -T tags
\T tags |
Set HTML table tag
options (width, border) |
O+ "tag"
(set OPTION_TABLETAG="tag";) |
| psql -U |
Specify database
username (default: admin) |
connect to DB user username; |
| psql -v name=val |
Set psql variable 'name'
to 'value' |
set option_name=value;
or
SHELL_VARIABLE=VALUE |
| psql -V |
Show version information
and exit |
?v |
| psql -W |
Prompt for password
(should happen automatically) |
type 'connect to DB;', then
Prompt for password is
hapen automatically |
psql -x
\x |
Turn on/off expanded table
output |
X{+|-}
(set OPTION_EXPANDED=[ON|OFF];) |
| psql -X |
Do not read startup
file (~/.psqlrc) |
-- |
| \C title |
table title |
C+ "string"
(set OPTION_CAPTION="title";) |
| \copy ... |
perform SQL COPY
with data stream
to the client machine |
copy TABLE() {from|to} FILE
using dekimiters 'DELIM'; |
| \d [table] |
describe table
(or view, index, sequence) |
?d [table]
(_list_relations)
(_list_relation table) |
| \d{t|i|s|v} |
list tables/indices/
sequences/views |
?dt, ?di, ?ds, ?dv
(_list_tables)
(_list_indexes)
(_list_sequnces)
(_list_view) |
| \d{p|S|l} |
list permissions/
system tables/lobjects |
?dp, ?dS, ?dl
(_list_permissions)
(_list_systables)
(_list_lobjects) |
| \da |
list aggregates |
?dA
(_list_aggregates) |
| \dd [object] |
list comment for
table, type, function,
or operator |
?dD [object]
(_list_adescriptions)
(_list_description object) |
| \df |
list functions |
?dF
(_list_functions) |
| \do |
list operators |
?dO
(_list_operators) |
| \dT |
list data types |
?dT
(_list_types) |
| \e [file] |
edit the current
query buffer or
[file] with external editor |
bash fc command
vi editor
emacs editor |
| \encoding |
set client encoding |
set CLIENT_ENCODING='encoding'; |
| \h [cmd] |
help on syntax of sql
commands, * for all commands |
??h [cmd]
( ??h for all command) |
| \i file |
read and execute
queries from file |
. file
(Use Bash dot command) |
\lo_export
\lo_import
\lo_unlink
\lo_list |
large object operations |
_lo_export
_lo_import
_lounlink
?dl (or _list_lobjects) |
| \p |
show the content of the current query buffer |
bash
history [+nlines] command |
| \q |
quit |
exit
(or Ctrl+D) |
| \qecho text |
write text to query
output stream |
bash
echo "text" command |
| \s [file] |
print history or
save it in [file] |
bash
history [+nlines] command
!! command
!<history_line_number> command
!<keyword> command |