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 Data Type Numeric Types Character Types Date and Time Types Boolean, Binary and UUID Types Casting Design Schema Normalization and Denormalization Data Integrity OLTP and OLAP Table relationships Aggregate Constraint Index B-Tree B-Tree Index B-Tree Index with order Using Hash Index GIN Index G...

Introduction In standard SQL, subqueries located in the JOIN clause operate independently, they cannot see or use data from tables located before (to the left of) it. When you add the LATERAL keyword, Postgres allows the subquery on the right to directly access column values of each row in the table on the left. Differences between Join types are as follows Standard CROSS JOIN takes the Cartesian product of 2 tables independent of each other (a multiplication of 2 tables without needing a condition) CROSS JOIN LATERAL operates like a for-each loop in programming, with each row in the left table, Postgres runs the subquery on the right to perform dynamic calculations repeatedly based on values from the left table and joins the results together. INNER JOIN is the intersection between 2 datasets, it only retains rows in 2 tables when both satisfy a specific join condition, the condition in ON is mandatory (unlike CROSS JOIN which does not require passing a condition) Can be combined...

Introduction In this article, we will continue exploring GIN Index with the JsonB data type. JsonB, short for JSON Binary, is a data type developed from the JSON data type and supported by PostgreSQL since version 9.2. The key difference between JSON and JsonB lies in how they are stored. JsonB supports binary storage and resolves the limitations of the JSON data type by optimizing the insert process and supporting indexing. Creating an Index When creating an index for a JsonB column, Postgres supports two strategies as follows: jsonb_ops : This is the default strategy that flattens the JSON to extract independent paths, keys and values to create Entries. For example, if you have a JSON like this: { "shop" : "ShopA" , "products" : [ { "product_name" : "mouse" , "price" : 50 } , { "product_name" : "keyboard" , "price" : 100 } ] } It will create a GIN Index as follows, n...