psql Commands
To see the database version
C:\Program Files\PostgreSQL\9.5\bin>psql –version
psql (PostgreSQL) 9.5.2
C:\Program Files\PostgreSQL\9.5\bin>
List Databases and then quit
C:\Program Files\PostgreSQL\9.5\bin>psql -l -U postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
———–+———-+———-+—————————-+—————————-+———————–
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
C:\Program Files\PostgreSQL\9.5\bin>
Getting Help for psql
C:\Program Files\PostgreSQL\9.5\bin>psql –help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]… [DBNAME [USERNAME]]
General options:
-c, –command=COMMAND run only single command (SQL or internal) and exit
-d, –dbname=DBNAME database name to connect to (default: “Administrator”)
-f, –file=FILENAME execute commands from file, then exit
-l, –list list available databases, then exit
-v, –set=, –variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, –version output version information, then exit
-X, –no-psqlrc do not read startup file (~/.psqlrc)
-1 (“one”), –single-transaction
execute as a single transaction (if non-interactive)
-?, –help[=options] show this help, then exit
–help=commands list backslash commands, then exit
–help=variables list special variables, then exit
Input and output options:
-a, –echo-all echo all input from script
-b, –echo-errors echo failed commands
-e, –echo-queries echo commands sent to server
-E, –echo-hidden display queries that internal commands generate
-L, –log-file=FILENAME send session log to file
-n, –no-readline disable enhanced command line editing (readline)
-o, –output=FILENAME send query results to file (or |pipe)
-q, –quiet run quietly (no messages, only query output)
-s, –single-step single-step mode (confirm each query)
-S, –single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, –no-align unaligned table output mode
-F, –field-separator=STRING
field separator for unaligned output (default: “|”)
-H, –html HTML table output mode
-P, –pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, –record-separator=STRING
record separator for unaligned output (default: newline)
-t, –tuples-only print rows only
-T, –table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, –expanded turn on expanded table output
-z, –field-separator-zero
set field separator for unaligned output to zero byte
-0, –record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, –host=HOSTNAME database server host or socket directory (default: “local socket”)
-p, –port=PORT database server port (default: “5432”)
-U, –username=USERNAME database user name (default: “Administrator”)
-w, –no-password never prompt for password
-W, –password force password prompt (should happen automatically)
For more information, type “\?” (for internal commands) or “\help” (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@postgresql.org>.
C:\Program Files\PostgreSQL\9.5\bin>
PGUSER Linux environment Variables
Holds user name to connect to the database
Normally unset it
unset PGUSER
Defaults DBs
- postgres : master DB to control RDBMS in Cluster databases.
- template0 — used to reconstruct template1 ,
- template1 – – copy of template0 is used to generate new DB s
Connect to Database interactively with postgres user
C:\Program Files\PostgreSQL\9.5\bin>psql –U postgres
psql (9.5.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page “Notes for Windows users” for details.
Type “help” for help.
postgres=# à This shows It is a super user.
SQL Specific Help
postgres=# \h
Available help:
ABORT DEALLOCATE
ALTER AGGREGATE DECLARE
ALTER COLLATION DELETE
ALTER CONVERSION DISCARD
ALTER DATABASE DO
ALTER DEFAULT PRIVILEGES DROP AGGREGATE
ALTER DOMAIN DROP CAST
ALTER EVENT TRIGGER DROP COLLATION
ALTER EXTENSION DROP CONVERSION
ALTER FOREIGN DATA WRAPPER DROP DATABASE
ALTER FOREIGN TABLE DROP DOMAIN
ALTER FUNCTION DROP EVENT TRIGGER
ALTER GROUP DROP EXTENSION
ALTER INDEX DROP FOREIGN DATA WRAPPER
ALTER LANGUAGE DROP FOREIGN TABLE
ALTER LARGE OBJECT DROP FUNCTION
ALTER MATERIALIZED VIEW DROP GROUP
ALTER OPERATOR DROP INDEX
ALTER OPERATOR CLASS DROP LANGUAGE
ALTER OPERATOR FAMILY DROP MATERIALIZED VIEW
ALTER POLICY DROP OPERATOR
ALTER ROLE DROP OPERATOR CLASS
ALTER RULE DROP OPERATOR FAMILY
ALTER SCHEMA DROP OWNED
ALTER SEQUENCE DROP POLICY
ALTER SERVER DROP ROLE
ALTER SYSTEM DROP RULE
ALTER TABLE DROP SCHEMA
ALTER TABLESPACE DROP SEQUENCE
ALTER TEXT SEARCH CONFIGURATION DROP SERVER
ALTER TEXT SEARCH DICTIONARY DROP TABLE
ALTER TEXT SEARCH PARSER DROP TABLESPACE
ALTER TEXT SEARCH TEMPLATE DROP TEXT SEARCH CONFIGURATION
ALTER TRIGGER DROP TEXT SEARCH DICTIONARY
ALTER TYPE DROP TEXT SEARCH PARSER
ALTER USER DROP TEXT SEARCH TEMPLATE
ALTER USER MAPPING DROP TRANSFORM
ALTER VIEW DROP TRIGGER
ANALYZE DROP TYPE
BEGIN DROP USER
CHECKPOINT DROP USER MAPPING
CLOSE DROP VIEW
CLUSTER END
COMMENT EXECUTE
COMMIT EXPLAIN
COMMIT PREPARED FETCH
COPY GRANT
CREATE AGGREGATE IMPORT FOREIGN SCHEMA
— More —
postgres=# \h CREATE TABLE
Command: CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_
{ column_name data_type [ COLLATE collation ] [ column_constraint [ … ] ]
| table_constraint
| LIKE source_table [ like_option … ] }
[, … ]
] )
[ INHERITS ( parent_table [, … ] ) ]
[ WITH ( storage_parameter [= value] [, … ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_
OF type_name [ (
{ column_name WITH OPTIONS [ column_constraint [ … ] ]
| table_constraint }
[, … ]
) ]
[ WITH ( storage_parameter [= value] [, … ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
Psql Specific Help
postgres-# \?
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
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
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
\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
Some Special Plsql Commands
postgres-# \l+ Show Detailed information about databases
postgres-# \du+ Return detailed list of users
List of roles
Role name | Attributes | Member of | Description
———–+————————————————————+———–+————-
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
postgres-# \! Returns to shell
postgres-# \q to quit
postgres=# select version();
version
————————————————————-
PostgreSQL 9.5.2, compiled by Visual C++ build 1800, 64-bit
(1 row)
psql –U postgres –f filename.sql to run a sql file
template1=# \c postgres to connect to anaother database
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page “Notes for Windows users” for details.
You are now connected to database “postgres” as user “postgres”.
postgres=#
\c DBNAME RemoteHOSTNAME connect to Remote Host and database
to connect to a database
C:\Program Files\PostgreSQL\9.5\bin>psql -U postgres template1
psql (9.5.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page “Notes for Windows users” for details.
Type “help” for help.
template1=#
List Database Objects
postgres=# \dS
postgres=# \dS+
List of relations
Schema | Name | Type | Owner | Size | Description
————+———————————+——-+———-+————+————-
pg_catalog | pg_aggregate | table | postgres | 48 kB |
pg_catalog | pg_am | table | postgres | 40 kB |
pg_catalog | pg_amop | table | postgres | 80 kB |
pg_catalog | pg_amproc | table | postgres | 64 kB |
pg_catalog | pg_attrdef | table | postgres | 8192 bytes |
pg_catalog | pg_attribute | table | postgres | 384 kB |
pg_catalog | pg_auth_members | table | postgres | 0 bytes |
pg_catalog | pg_authid | table | postgres | 40 kB |
pg_catalog | pg_available_extension_versions | view | postgres | 0 bytes |
pg_catalog | pg_available_extensions | view | postgres | 0 bytes |
pg_catalog | pg_cast | table | postgres | 48 kB |
pg_catalog | pg_class | table | postgres | 136 kB |
pg_catalog | pg_collation | table | postgres | 40 kB |
pg_catalog | pg_constraint | table | postgres | 48 kB |
pg_catalog | pg_conversion | table | postgres | 56 kB |
pg_catalog | pg_cursors | view | postgres | 0 bytes |
pg_catalog | pg_database | table | postgres | 8192 bytes |
pg_catalog | pg_db_role_setting | table | postgres | 8192 bytes |
pg_catalog | pg_default_acl | table | postgres | 0 bytes |
.
Introduction
Database Security 
Reply