How To Dev

Introduction PostgreSQL is a powerful open-source relational database management system, developed with many powerful features, high performance and famous for its reliability applied across various diverse projects. The outstanding advantages include: High data integrity and full ACID compliance. Powerful extensibility, allowing users to define their own data types, functions and custom indexes. Support for a wide variety of data types from structured (SQL) to unstructured (JSON/JSONB, XML). A strong development community, continuously updated and optimally secure. Detail Installing PostgreSQL with Docker Explanation of PostgreSQL Operations TOAST Storage Strategies Common PostgreSQL Statements PostgreSQL User Creation and Least Privilege Role Assignment Design schema Normalization and Denormalization Data Integrity OLTP and OLAP Index B-Tree Index Using Hash Index GIN Index GIN Index with Array GIN Index with TEXT GIN Index with JsonB GiST Index SP-GiST Index BRIN Index Composite Ind...

Introduction Subquery : A SELECT statement located inside another SQL statement (can be inside SELECT, FROM, WHERE, HAVING ). It supplies data for the main query. Nested Query : A term used to describe the structure of the query. When a Subquery resides inside a parent statement, this action is called nesting. Thus, Subquery can be viewed as the component (the child), while Nested query is the structural relationship (the parent containing the child). A statement that contains a subquery has its entire structure referred to as a nested query . Classification Non-correlated Subquery : This type of subquery runs completely independently of the parent statement. Postgres executes this subquery exactly once, using its result to apply to the parent statement. Example: SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) Here, SELECT AVG(salary) FROM employees is an independent Subquery that only needs to run once to provide the value for the outer ...

Introduction This article covers several common statements used in PostgreSQL, which are highly fundamental and frequently applied in almost all projects utilizing a database. General Concepts In SQL generally and PostgreSQL specifically, the terms Statement, Query and Clause are used very frequently, yet they remain technically distinct with clear boundaries and hierarchies. A Statement is like a complete sentence in a text. It is a fully executable, independent unit within a database. It begins with an action keyword and typically ends with a semicolon ; in execution. Examples include INSERT INTO products (name) VALUES ('Product name'); or an entire SELECT... block. A Query is a question, regarded as a special type of statement. It is a special case of a statement because it is only used to read data, not modify it. It mainly refers to the SELECT statement, such as SELECT email FROM customers WHERE id = 1; A Clause is like a phrase or clause that constructs the senten...