How To Dev

Introduction Besides the clauses mentioned in the previous article, PostgreSQL, as an exceptionally powerful database management system, supports many other specialized clauses as follows. Navigation and Pagination Group This is a basic yet crucial group that is very widely used. ORDER BY : Sorts the returned results in ascending ( ASC ) or descending ( DESC ) order. There is also ORDER BY col DESC NULLS LAST to push NULL values to the very bottom of the result table instead of the default top when sorting in descending order. LIMIT/OFFSET : Used for pagination. LIMIT N retrieves a maximum of N rows. OFFSET M skips the first M rows before retrieving. When you use OFFSET M , PostgreSQL must still scan and count all the first M rows to know where to begin, then it retrieves the next N rows for LIMIT . The consequence is that if OFFSET is small (for the first pages), the query still runs very fast. If OFFSET is large, such as using OFFSET 100000 , the database must expend resour...

Introduction This is a concept created by Postgres as a set of behavior rules to decide whether other people can see or edit data when someone is modifying it. The isolation level only makes sense and only works when accompanied by a Transaction. This is the factor that ensures Isolation, helping to decide how parallel transactions handle operations independently of each other. When you execute a single statement like UPDATE products SET price = 100, Postgres activates Autocommit mode to automatically wrap the statement with BEGIN...COMMIT. It automatically creates a Transaction for extremely fast execution. Levels There are currently 4 levels ranging from the most relaxed to the strictest. The stricter the level, the more accurate the data, but the system runs slightly slower. Read Uncommitted This is the most relaxed level, allowing you to see what others are drafting even if they have not yet committed. Because of that characteristic, using it is very dangerous as it causes Dirty Re...