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

This is such a good link it's worthy of a dedicated button!

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 set

Bibliography & References