How To Dev

Introduction In PostgreSQL (and standard SQL in general), Aggregate Constraint is not an officially supported syntax or keyword, but rather a concept referring to constraints based on aggregated data (Aggregate Constraint / Cross-row Constraint), which is a very common problem in data processing. In practice, PostgreSQL directly prohibits using Aggregate Functions such as SUM, COUNT, AVG, MAX, MIN inside a table's CHECK constraint. The reason is that a CHECK constraint is evaluated on individual rows upon insertion or modification, whereas aggregate functions compute values across sets of multiple rows. For example, you cannot use CHECK with SUM to create a constraint ensuring the total revenue percentage of categories does not exceed 100% . Postgres will throw an error regarding the use of aggregate functions within a CHECK Constraint . Alternative Solutions To enforce an aggregate constraint rule, you can apply the following solutions: Using Triggers This is the most commo...

Introduction When using older versions of Postgres, only Functions were available. However, from version 11 onwards, Postgres has officially added support for Procedures to address the limitations that Functions could not overcome. While their implementations are quite similar, they possess fundamental core differences: Transaction Control The biggest difference is that a Procedure allows you to have full control over transactions. You can use COMMIT or ROLLBACK directly inside the body of the procedure. This is extremely useful when you need to process large amounts of data in batches without worrying about memory overflow or locking tables for too long. Conversely, a Function cannot contain COMMIT or ROLLBACK. The entire Function must execute within a single transaction. If a command at the end of the function fails, all previous commands are completely rolled back. Return Value Using a Function requires returning a certain value, even if it is just void, a single value, or a TABLE d...

Introduction Functions are an extremely important concept in database-side programming, which are a collection of SQL or PL/pgSQL statements encapsulated into a single block of code that can be reused multiple times It acts like a function in other programming languages such as JavaScript and Python and it only runs when explicitly invoked using a SELECT or CALL statement or when nested inside another function. Use cases Functions in PostgreSQL are highly flexible and can be applied to many real-world problems in programming, typically serving the following main purposes: Encapsulation Instead of forcing the backend application to send a long SQL query spanning hundreds of lines with multiple JOIN and GROUP BY clauses, you can hide all that complexity inside a Function. Examples include revenue reports and performance statistics. Data Transformation This is ideal when you need to perform mathematical calculations, process strings, or execute complex IF/ELSE logic on data before returni...