How To Dev

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...

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...