Playing with psql in PostgreSQL DB

How to connect PostgreSQL Database

C:\Users\amits>psql -U postgres

Password for user postgres:

psql (16.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.

Check list of databases

postgres=# \l

List of databases

-------------+----------+----------+-----------------+----------------------------+----------------------------+------------+-----------+-----------------------

(6 rows)

Switch to other database

postgres=# \c db1

You are now connected to database "db1" as user "postgres".

db1=# \c amit

You are now connected to database "amit" as user "postgres".

Need to run windows command in psql

amit=# \! dir

Volume in drive C is Windows-SSD

Volume Serial Number is 8E9F-F0FD

Directory of C:\Users\amits

List of table

amit=# \dt

List of relations

Schema | Name | Type | Owner

--------+------+-------+-------

public | tree | table | amit

(1 row)

Size of table

amit-# \dt+

List of relations

--------+------+-------+-------+-------------+---------------+------------+-------------

(1 row)

Structure of table

amit-# \d tree

Table "public.tree"

Column | Type | Collation | Nullable | Default

--------+------------------------+-----------+----------+---------

type | character varying(100) | | |

Schema in database

amit-# \dn

List of schemas

Name | Owner

--------------+-------------------

amit_schema1 | postgres

amit_schema2 | postgres

public | pg_database_owner

(3 rows)

Schema details in database

amit-# \dn+

List of schemas

Name | Owner | Access privileges | Description

--------------+-------------------+----------------------------------------+------------------------

amit_schema1 | postgres | |

amit_schema2 | postgres | |

public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema

| | =U/pg_database_owner |

(3 rows)

Functions in database

amit-# \df

List of functions

Schema | Name | Result data type | Argument data types | Type

--------+------+------------------+---------------------+------

(0 rows)

Details of function in database

amit-# \df+

List of functions

--------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+-------------

(0 rows)

Views in Database

amit-# \dv

Details of Views in Database

Did not find any relations.

amit-# \dv+

Did not find any relations.

List of users in database

amit-# \du

List of roles

Role name | Attributes

-----------+------------------------------------------------------------

amit | Superuser

amit2 | Create DB

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS

test |

Sequence in Database

amit-# \ds

Did not find any relations.

Details of sequence in Database

amit-# \ds+

Did not find any relations.

amit=# select * from tree;

type

--------------------

this is a big tree

time

(2 rows)

Rerun same command again

amit=# \g

type

--------------------

this is a big tree

time

(2 rows)

Need to check help details

amit=# \?

General

\bind [PARAM]... set query parameters

\copyright show PostgreSQL usage and distribution terms

\crosstabview [COLUMNS] execute query and display result in crosstab

\errverbose show most recent error message at maximum verbosity

\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);

\g with no arguments is equivalent to a semicolon

\gdesc describe result of query, without executing it

\gexec execute query, then execute each value in its result

\gset [PREFIX] execute query and store result in psql variables

\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode

\q quit psql

\watch [[i=]SEC] [c=N] execute query every SEC seconds, up to N times

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

\ev [VIEWNAME [LINE]] edit view 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 [-n] [STRING] write string to standard output (-n for no newline)

\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 [-n] [STRING] write string to \o output stream (-n for no newline)

\warn [-n] [STRING] write string to standard error (-n for no newline)

Conditional

\if EXPR begin conditional block

\elif EXPR alternative within current conditional block

\else final alternative within current conditional block

\endif end conditional block

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

\dA[+] [PATTERN] list access methods

\dAc[+] [AMPTRN [TYPEPTRN]] list operator classes

\dAf[+] [AMPTRN [TYPEPTRN]] list operator families

\dAo[+] [AMPTRN [OPFPTRN]] list operators of operator families

\dAp[+] [AMPTRN [OPFPTRN]] list support functions of operator families

\db[+] [PATTERN] list tablespaces

\dc[S+] [PATTERN] list conversions

\dconfig[+] [PATTERN] list configuration parameters

\dC[+] [PATTERN] list casts

\dd[S] [PATTERN] show object descriptions not displayed elsewhere

\dD[S+] [PATTERN] list domains

\ddp [PATTERN] list default privileges

\dE[S+] [PATTERN] list foreign tables

\des[+] [PATTERN] list foreign servers

\det[+] [PATTERN] list foreign tables

\deu[+] [PATTERN] list user mappings

\dew[+] [PATTERN] list foreign-data wrappers

\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]

list [only agg/normal/procedure/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[S+] [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+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]

list operators

\dO[S+] [PATTERN] list collations

\dp[S] [PATTERN] list table, view, and sequence access privileges

\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]

\drds [ROLEPTRN [DBPTRN]] list per-database role settings

\drg[S] [PATTERN] list role grants

\dRp[+] [PATTERN] list replication publications

\dRs[+] [PATTERN] list replication subscriptions

\ds[S+] [PATTERN] list sequences

\dt[S+] [PATTERN] list tables

\dT[S+] [PATTERN] list data types

\du[S+] [PATTERN] list roles

\dv[S+] [PATTERN] list views

\dx[+] [PATTERN] list extensions

\dX [PATTERN] list extended statistics

\dy[+] [PATTERN] list event triggers

\l[+] [PATTERN] list databases

\sf[+] FUNCNAME show a function's definition

\sv[+] VIEWNAME show a view's definition

\z[S] [PATTERN] same as \dp

Large Objects

\lo_export LOBOID FILE write large object to file

\lo_import FILE [COMMENT]

read large object from file

\lo_list[+] list large objects

\lo_unlink LOBOID delete a large object

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

numericlocale|pager|pager_min_lines|recordsep|

recordsep_zero|tableattr|title|tuples_only|

unicode_border_linestyle|unicode_column_linestyle|

unicode_header_linestyle)

\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 "amit")

\conninfo display information about current connection

\encoding [ENCODING] show or set client encoding

\password [USERNAME] securely change the password for a user

Operating System

\cd [DIR] change the current working directory

\getenv PSQLVAR ENVVAR fetch environment variable

\setenv NAME [VALUE] set or unset environment variable

\timing [on|off] toggle timing 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

Check timing of Running SQL

amit=# \timing

Timing is on.

amit=# select * from tree;

type

--------------------

this is a big tree

time

(2 rows)

Time: 0.342 ms

Want to off the sql time generate

amit=# \timing

Timing is off.

amit=# \g

type

--------------------

this is a big tree

time

(2 rows)

Edit file in PostgreSQL

amit=# \e

type

--------------------

this is a big tree

time

(2 rows)

Get all the functions

amit=# \df

List of functions

Schema | Name | Result data type | Argument data types | Type

--------+------+------------------+---------------------+------

(0 rows)

Edit function in PostgreSQL

amit=# \ef

amit=# CREATE FUNCTION ( )

RETURNS

LANGUAGE

-- common options: IMMUTABLE STABLE STRICT SECURITY DEFINER

AS $function$

$function$

amit-# \df

List of functions

Schema | Name | Result data type | Argument data types | Type

--------+------+------------------+---------------------+------

(0 rows)

amit-# \ef function_name

How to execute command from .sql file in PostgreSQL

C:\Users\amits>psql -U postgres -d postgres -f insert.sql

Password for user postgres:

psql: error: insert.sql: No such file or directory

C:\Users\amits>psql -U postgres

Password for user postgres:

psql (16.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=# ]c amit

postgres-# \c amit

You are now connected to database "amit" as user "postgres".

amit=# select * from tree;

type

--------------------

this is a big tree

time

(2 rows)

Generate output of query in output file

amit=# \o output

amit=# select * from tree;

amit=# \q

C:\Users\amits>more output

type

--------------------

this is a big tree

time

(2 rows)

How to work with Autocommit on or off in PostgreSQL

C:\Users\amits>psql -U postgres

Password for user postgres:

psql (16.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=# \set AUTOCOMMIT off

amit=# insert into tree values ('Checking commit');

INSERT 0 1

amit=*#

amit=*# select * from tree;

type

--------------------

this is a big tree

time

Checking commit

(3 rows)

amit=*# rollback;

ROLLBACK

amit=#

amit=# select * from tree;

type

--------------------

this is a big tree

time

(2 rows)

PostgreSQL DBA Support

Search This Blog