PostgreSQL - cheatsheat for most popular commands

Recently, more and more people have to deal with PostgreSQL in their work. And comparing this database with others, you come to the conclusion that the syntax of SQL is slightly different from the generally accepted standards. Faced with such a problem, I decided to write a mini-cheat sheet for the most common commands that any developer will need.

Just like in Oracle, all database manipulation (DML) is done through the system tables and the procedures and views that bind them. There is neither show databases nor show create table. But DML, at the same time, is transactional - you can roll back the transaction that creates the database, for example.

PostgreSQL installation

Try to find dockers for PostgreSql:

docker search postgres

Installing Postgre:

docker run --rm --name postgres -e POSTGRES_PASSWORD=my_pass -d -p 5432:5432 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data  postgres

Connecting to sql through user homestead

psql -U homestead -h localhost

Connecting to database test_db

\c test_db;

Exit from database:

\q

Show list of databases:

\l

List of schemes:

\dn

List of all storage procedures and functions:

\df

List of all views:

\dv

List of tables:

\dt

List of tables with additional information:

\dt+

Info about table:

\d+ table_name

Info about function:

\df+ function_name

Prettify response:

\x

List of users:

\du

Help about commands:

\?

List of tablespaces:

\db

Tablespace - Allows you to organize the logic of placing database object files in the file system.

By default, the following are created:

• pg_default - used by default for the template1 and template0 databases

• pg_global - Used for general system catalogs.

• pg_tblspc - contains links to new tablespaces.

Show the current version of the database:

select version();

Show additional attributes about row:

select xmin,xmax,cmin,cmax,ctid from tbl;

ctid is a link to the next newer version of the same line. For the most recent version of the line, the ctid refers to that version itself.

Total freeze:

VACUUM FREEZE

VACUUM FULL

vacuumdb --all --freeze

Show of system files:

\dS+

Monitoring:

\d pg_stat_activity

SELECT * FROM pg_stat_activity;

\d pg_database

SELECT * FROM pg_database;

\d pg_class

SELECT * FROM pg_class;

CREATE EXTENSION pg_visibility;

Getting ID of database

SELECT txid_current();

Show all locks of transactions:

SELECT * FROM pg_locks;

Config file location: /var/lib/postgresql/data/postgresql.conf