Important PostgreSQL Concepts - 16 psql Command-line Utilities You Don't Want To Miss And A Lot More

PostgreSQL is one of the most advanced database in the open source relational database market.According to db-engines, it is the fourth most used database at the time of writing.

This blog is more of a cheat sheet to quickly know about PostgreSQL and get started with it.

So...lets get started !!

Features of Postgres

• It is an object relational database.
• It handles concurrency better than MySQL.
• It implements Multiversion Concurrency Control (MVCC) without read locks.
• It protects data integrity at transaction level.
• Less vulnerable to data corruption.
• Supports parallel query plan that can use multiple CPU/cores.
• It can create partial indexes.
• It can create indexes in non-blocking way.
• Allows us to add our own datatypes, operators and index types.
• Capable of writing large amounts of data more efficiently.
• It supports table inheritance.

Disadvantages of Postgres

• Slow for read-heavy operations compared to MySQL.
• It forks a new process for each new client connection which allocates a non-trivial amount of memory (about 10 MB).

PS: If you found some words fancy in the above list, I would suggest you to quickly google about it to get an idea. This tip applies to everything in this blog. You can mention these points in your interviews as well 😎

---

Server and database objects provided by PostgreSQL

1. Server service
2. Databases -> container of objects like tables, views, functions, indexes.
3. Tables -> Stores actual data. Belongs to a database.
4. Schema -> Logical container of table and other objects.
5. Tablespaces - place where data is stored physically Provides 2 default tablespaces:
   • pg_default -> for storing user data
   • pg_global -> for storing system data
6. Views -> views are named queries stored in the database.
7. Functions
8. Operators
9. Casts -> to convert one datatype to another.
10. Sequence -> used to manage auto-increment columns in a table.

---

Evaluation order in Queries

The following sequence shows the decreasing priority(from left to right) given to different clauses in a complex SQL Query.

FROM --> WHERE --> SELECT --> ORDER BY

---

Most Used Clauses

• IN - Returns true if a value matches any value in the list
• BETWEEN - Return true if a value is between a range of values ex. BETWEEN 3 AND 5
• LIKE - Return true if a value matches a pattern ex. LIKE 'Aud%' means match string starting with 'Aud'.
• ILIKE - Same as LIKE but case insensitive pattern matching. Notation (~~*)
• OFFSET - Skip a number of rows before returning the resultset
• LIMIT - To constrain the number of rows returned by a query (not a SQl standard)
• FETCH - Same as LIMIT and it a SQL standard. Syntax -> FETCH {FIRST|NEXT} row_cnt {ROW|ROWS} ONLY
• LEFT(s, n) - Extracts first n characters from s
• ALL - We can use the word ALL to allow >= or > or < or <= to act over a list ex. on result of select subquery
• ORDER BY - Sort thr column in ASC or DESC order
• GROUP BY - Functions such as SUM and COUNT are applied to groups of items sharing values.ex."GROUP BY continent" ,the result is only one row for each different value of continent. All the other columns must be "aggregated" by one of SUM, COUNT etc.
• WHERE - filters the rows before aggregation operation
• HAVING - filters after the agregation

---

Some important psql commands

1. sudo -u <role name> psql -> switched to and starts psql command prompt
2. \c dbname username -> switch connection to new database (dbname) under a user specified by
3. \l -> list all available databases
4. \dt -> list all tables in current database
5. \d table_name -> describe a table
6. \dn -> list all Schemas of current database
7. \df -> list all functions of current database
8. \dv -> list all Views of current database
9. \du -> list all users and their assigned roles

10. psql -h localhost -U username db_name; -> switch user and database

11. \s -> to display command history

12. \g -> to execute previous command

13. \i filename -> to execute psql commands from a file

14. \timing -> to turn ON/OFF query execution time:w

15. \e -> to write command in default editor(vim/nano)

16. \q -> to quit psql

---

Common Queries

• Create new user -> CREATE USER <username> WITH PASSWORD 'password';
• Drop user -> DROP USER IF EXISTS <username>;
• Create new database -> CREATE DATABASE <db_name>;
• Grant privileges -> GRANT ALL|SELECT|UPDATE|DELETE PRIVILEGES ON DATABASE <db_name> TO <username>;
• Drop database -> DROP DATABASE IF EXISTS <db_name>;
• Create schema -> CREATE SCHEMA <schema_name> [CASCADE];
• Create table -> CREATE TABLE <tb_name>;
• Insert data -> INSERT INTO tb_name(col1, col2,...) VALUES (val1, val2,...);
• Update Column -> UPDATE <tb_name> SET col_name = value WHERE <condition>;
• Delete row -> DELETE FROM <tb_name> WHERE <condition>;
• Delete table -> DROP TABLE IF EXISTS <tb_name>;

---

Resources And References

This is one of the most awesome resource I have found to know anything about using PostgreSQL:

• https://www.postgresqltutorial.com/

Wrap Up

I hope you found this cheat sheet helpful and keep it handy for a quick reference. If you want me to add more information in this blog or need any help please let me know in the comment section.

Let's connect on LinkedIn

👋 Thanks for reading, See you next time