psql 101 - Introduction to PostgreSQL Shell for Database Interactive Terminal
psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file.psql shell command
List exist databases
$ psql --list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------------------+-------+----------+-------------+-------------+-------------------
dvdrental | dbch | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | dbch | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | dbch | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/dbch +
| | | | | dbch=CTc/dbch
template1 | dbch | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/dbch +
| | | | | dbch=CTc/dbch
(4 rows)
Conntect to database
$ psql <dbname>
Load SQL from file
$ psql < file.sql
psql interactive command
\? - Get help
# \?
General
\copyright show PostgreSQL usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pipe)
\gset [PREFIX] execute query and store results in psql variables
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
\watch [SEC] execute query every SEC seconds
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\ddp [PATTERN] list default privileges
\dD[S+] [PATTERN] list domains
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dE[S+] [PATTERN] list foreign tables
\dx[+] [PATTERN] list extensions
\dy [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\sf[+] FUNCNAME show a function's definition
\z [PATTERN] same as \dp
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset [NAME [VALUE]] set table output option
(NAME := {border|columns|expanded|fieldsep|fieldsep_zero|
footer|format|linestyle|null|numericlocale|pager|
recordsep|recordsep_zero|tableattr|title|tuples_only})
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off|auto] toggle expanded output (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "dvdrental")
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
\conninfo display information about current connection
Operating System
\cd [DIR] change the current working directory
\setenv NAME [VALUE] set or unset environment variable
\tidbch [on|off] toggle tidbch of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
\q - Exit psql shell
To exit psql shell, use Ctrl+D or \q:
dvdrental=# \q
\d - List tables, views, and sequences
dvdrental=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------------+----------+-------
public | actor | table | dbch
public | actor_actor_id_seq | sequence | dbch
public | actor_info | view | dbch
public | address | table | dbch
public | address_address_id_seq | sequence | dbch
public | category | table | dbch
public | category_category_id_seq | sequence | dbch
public | city | table | dbch
public | city_city_id_seq | sequence | dbch
public | country | table | dbch
public | country_country_id_seq | sequence | dbch
public | customer | table | dbch
public | customer_customer_id_seq | sequence | dbch
public | customer_list | view | dbch
public | film | table | dbch
public | film_actor | table | dbch
public | film_category | table | dbch
public | film_film_id_seq | sequence | dbch
public | film_list | view | dbch
public | inventory | table | dbch
public | inventory_inventory_id_seq | sequence | dbch
public | language | table | dbch
public | language_language_id_seq | sequence | dbch
public | nicer_but_slower_film_list | view | dbch
public | payment | table | dbch
public | payment_payment_id_seq | sequence | dbch
public | rental | table | dbch
public | rental_rental_id_seq | sequence | dbch
public | sales_by_film_category | view | dbch
public | sales_by_store | view | dbch
public | staff | table | dbch
public | staff_list | view | dbch
public | staff_staff_id_seq | sequence | dbch
public | store | table | dbch
public | store_store_id_seq | sequence | dbch
(35 rows)
\db - List table spaces
management_development=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | dbch |
pg_global | dbch |
(2 rows)
\dt - List tables
dvdrental=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+-------
public | actor | table | dbch
public | address | table | dbch
public | category | table | dbch
public | city | table | dbch
public | country | table | dbch
public | customer | table | dbch
public | film | table | dbch
public | film_actor | table | dbch
public | film_category | table | dbch
public | inventory | table | dbch
public | language | table | dbch
public | payment | table | dbch
public | rental | table | dbch
public | staff | table | dbch
public | store | table | dbch
(15 rows)
Check table schema
PostgreSQL store table schema in information_schema,
use following SQL to get column definition for specific table:
dvdrental=# SELECT column_name, data_type, is_nullable, column_default \
FROM information_schema.columns \
WHERE table_name = 'customer';
column_name | data_type | is_nullable | column_default
-------------+-----------------------------+-------------+-----------------------------------------------
customer_id | integer | NO | nextval('customer_customer_id_seq'::regclass)
store_id | smallint | NO |
first_name | character varying | NO |
last_name | character varying | NO |
email | character varying | YES |
address_id | smallint | NO |
activebool | boolean | NO | true
create_date | date | NO | ('now'::text)::date
last_update | timestamp without time zone | YES | now()
active | integer | YES |
(10 rows)
References
Feedback
Was this page helpful?
Glad to hear it!
Sorry to hear that.