PSQL

apt install postgresql-client-xx

root@depgsql01:~# su - postgres

postgres@depgsql01:~$ psql
psql (13.6 (Debian 13.6-1.pgdg110+1))
Type “help” for help.

postgres=# select version();
version
--------------------------------------------------------------
PostgreSQL 13.6 (Debian 13.6-1.pgdg110+1) on x86_64-pc-linuxgnu,
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

---

It allows running SQL commands, PL/pgSQL blocks and administrating the database.
Although there are many graphical interface programs to manage the database, it is
useful to be familiar with the terminal (command prompt).
After adding the appropriate repository for your version from the postgresql.org/download link, you can install it with the following command.

Note: Client package is automatically installed when postgresql server is installed.

Transactions that can be made using PSQL:

• Execute, edit, store and retrieve SQL commands and PL/pgSQL blocks.
• Can format query results in a report layout.
• Can list column definitions for any table.
• Access and copy the data in the database.

If we are connected to the operating system with root user, we first switch to the
postgres user –> su - postgres
Then we run the psql command (on postgres database server).

\?              # psql help.
\conninfo       # The connected database and port can be seen.
\h              # Help with SQL commands.
\h alter table  # Help with the alter table command.
\l              # Lists the databases.

# Connection to a different database
psql -d veritabanı_adı -U kullanıcı_adı –W

# Example
psql -d dvdrental -U postgres -W

# Example1: to connect to a database located on another machine
psql -h 10.71.6.22 -d dvdrental -U dbuser -W

# Example 2
psql -h 10.71.6.22 –p 5433 -d dvdrental -U dbuser -W

# To avoid entering password every time
PGPASSWORD="şifre" psql -h 10.71.6.22 –p 5433 -d dvdrental -U dbuser

---

Note: In order to connect to the database on the network, the security settings in
the pg_hba.conf file, the operating system setting (iptables), and the listen_addresses settings in the postgressql.conf must be set correctly (described in chapters 3 and 4).

In Example 1, it sends a connection request from the default port 5432 to the database
named dvdrental on the 10.71.6.22 server. By entering the password, a connection to
the database is established.
If Postgresql is using a different port other than 5432. (You can see from which port it is
using by executing netstat –ntlp on the database server.)

In Example 2, if Postgresql is using 5433 port, the connection is established by adding
the ‘-p 5433’ port parameter

To switch to a different database
\c dvdrental
\c database_name -U user_name
\d                # Lists all tables(relations).
\d+               # Lists tables with additional information.
\d+ table_isim    # Table information.
\dt               # Lists the tables in the #public schema.
\dt tests.*       # Lists the tables in the #tests schema.
\dn               # Lists the schemas in the connected database.
\dv               # Lists views.
\du               # Lists users (user/role).
\dp+ table_isim   # Lists table access rights.

---

dvdrental=# \d+ city
Table “public.city”
Column|Type|Collation|Nullable|Default|Storage|Stats
target|Description
-----------+-------------+-----------+----------+------------+----------+----
city_id | integer | | not null | | plain | |
city | character v | | not null | | extended | |
country_id | smallint | | not null | | plain | |
last_update| timestamp wi| | not null | | plain | |
Indexes:
“city_pkey” PRIMARY KEY, btree (city_id)
“idx_fk_country_id” btree (country_id)
Foreign-key constraints:
“fk_city” FOREIGN KEY (country_id) REFERENCES
country(country_id)
Referenced by:
TABLE “address” CONSTRAINT “fk_address_city” FOREIGN KEY
(city_id) REFERENCES city(city_id)
Triggers:
last_updated BEFORE UPDATE ON city FOR EACH ROW EXECUTE
FUNCTION last_updated()
Access method: heap

---

SELECT version(); #PostgreSQL version

\g                #To run the previous command again

\s                #To see command history


# To save command history to a file.

\s command_history.txt


# To run psql commands from a file.

\i file_name.sql


# Returns how long it took to execute SQL statement.

\timing on/off


# Provides the opportunity to open and edit SQL with the operating system’s file editor.

\e


# Runs the last SQL executed every 5 seconds.

\watch 5


\H              # Gives the output in html format.



\! ifconfig     # Operating system commands.



#Exit

\q
\exit
\quit