How to Query a Category Tree in PostgreSQL with a Recursive CTE
In a PostgreSQL category table, each category can store its parent’s ID in the same table. To get a category and all the categories below it, PostgreSQL follows…
Read articleTopic
SQL reads and changes data in relational databases. Queries describe the requested result while the database decides how to produce it.
In a PostgreSQL category table, each category can store its parent’s ID in the same table. To get a category and all the categories below it, PostgreSQL follows…
Read articleWhen you insert a row into a MySQL table, you may need to add it if no row has its key, or update the row that already has…
Read articleWhen a PostgreSQL table stores several events for each account, you may need the newest event for each account. DISTINCT ON is PostgreSQL’s concise way to choose one…
Read articleWhen a MySQL database responds slowly, the SQL statements taking the most time are a useful place to investigate. The slow query log records statements that exceed a…
Read articlePostgreSQL uses multiversion concurrency control, or MVCC, to let transactions read consistent data while other transactions change it. When a transaction updates or deletes a row, PostgreSQL keeps…
Read articleWhen a PostgreSQL table has many rows and queries often target a smaller group, a partial index helps PostgreSQL find those rows without indexing the whole table. A…
Read articleWhen you add a composite index to a MySQL table, the order of its columns determines which queries can use it efficiently. A composite index is one index…
Read articleWhen a PostgreSQL query runs slowly, its EXPLAIN ANALYZE output shows how PostgreSQL ran it and how many rows each step processed. A query plan is a tree…
Read articleOn a PostgreSQL table serving a live web application, a regular index build can cause an outage because it holds a lock while PostgreSQL scans the table and…
Read article