This query (unlike the currently accepted answer by Joel) does not treat NULL values as equal. How to query a json column for empty objects?. ![]() The data type of the column must support the operator. If status is defined NOT NULL, you can simplify to: AND status 'ACTIVE' Add another WHERE condition like demonstrated above to avoid this and make it even faster: Exclude empty updatesįor rows that already have status = 'ACTIVE' this update would not change anything, but still insert a new row version at full cost (minor exceptions apply). With lots of duplicates this becomes way more efficient. For a base table with few duplicates this is only mildly more efficient. The subquery in the EXISTS anti-semi-join can stop evaluating as soon as the first dupe is found (no point in looking further). I suggest a serial or an IDENTITY column in Postgres 10+. If you don't have a primary or unique key for the table ( id in the example), you can substitute with the system column ctid for the purpose of this query (but not for some other purposes): AND s1.ctid s.ctidĮvery table should have a primary key. SELECT FROM sales s1 - SELECT list can be empty for EXISTSĪND s.status IS DISTINCT FROM 'ACTIVE' - avoid empty updates. EXISTS is standard SQL, has been around forever (at least since PostgreSQL 7.2, long before this question was asked) and fits the presented requirements perfectly: UPDATE sales s Use a NOT EXISTS (anti-)semi-join for even better performance. Nukes the performance of the currently accepted answer by factor 10 - 15 (in my tests on PostgreSQL 8.4 and 9.1).īut this is still far from optimal. Which is much faster than either of them. If you put together the answers so far, clean up and improve, you would arrive at this superior query: UPDATE sales
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |