PostgreSQL - cheatsheat for most popular commands

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:


Show list of databases:


List of schemes:


List of all storage procedures and functions:


List of all views:


List of tables:


List of tables with additional information:


Info about table:

\d+ table_name

Info about function:

\df+ function_name

Prettify response:


List of users:


Help about commands:


List of tablespaces:


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:



vacuumdb --all --freeze

Show of system files:



\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

Popular Posts

My most popular posts

Maximum productivity on remote job

Maximum productivity on remote job

I started my own business and intentionally did my best to work from anywhere in the world. Sometimes I sit with my office with a large 27-inch monitor in my apartment in Cheboksary. Sometimes I’m in the office or in some cafe in another city.

Hello! I am Sergey Emelyanov and I am hardworker
Business PHP

Hello! I am Sergey Emelyanov and I am hardworker

I am a programmer. I am an entrepreneur in my heart. I started making money from the age of 11, in the harsh 90s, handing over glassware to a local store and exchanging it for sweets. I earned so much that was enough for various snacks.

Hire Professional CRM developer for $25 per hour

I will make time for your project. Knowledge of Vtiger CRM, SuiteCRM, Laravel, and Vue.js. I offer cooperation options that will help you take advantage of external experience, optimize costs and reduce risks. Full transparency of all stages of work and accounting for time costs. Pay only development working hours after accepting the task. Accept PayPal and Payoneer payment systems. How to hire professional developer? Just fill in the form