RSS

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

DNS Firewall for iOS

Encrypted your DNS to protect your privacy and firewall to block phishing, malicious domains, block ads in all browsers and apps

Ad