sql - IN vs ANY operator in PostgreSQL

ID : 20025

viewed : 31

Tags : sqldatabasepostgresqlrdbmssql-insql

Top 5 Answer for sql - IN vs ANY operator in PostgreSQL

vote vote

96

(Neither IN nor ANY is an "operator". A "construct" or "syntax element".)

Logically, quoting the manual:

IN is equivalent to = ANY.

But there are two syntax variants of IN and two variants of ANY. Details:

IN taking a set is equivalent to = ANY taking a set, as demonstrated here:

But the second variant of each is not equivalent to the other. The second variant of the ANY construct takes an array (must be an actual array type), while the second variant of IN takes a comma-separated list of values. This leads to different restrictions in passing values and can also lead to different query plans in special cases:

ANY is more versatile

The ANY construct is far more versatile, as it can be combined with various operators, not just =. Example:

SELECT 'foo' LIKE ANY('{FOO,bar,%oo%}'); 

For a big number of values, providing a set scales better for each:

Related:

Inversion / opposite / exclusion

"Find rows where id is in the given array":

SELECT * FROM tbl WHERE id = ANY (ARRAY[1, 2]); 

Inversion: "Find rows where id is not in the array":

SELECT * FROM tbl WHERE id <> ALL (ARRAY[1, 2]); SELECT * FROM tbl WHERE id <> ALL ('{1, 2}');  -- equivalent array literal SELECT * FROM tbl WHERE NOT (id = ANY ('{1, 2}')); 

All three equivalent. The first with array constructor, the other two with array literal. The data type can be derived from context unambiguously. Else, an explicit cast may be required, like '{1,2}'::int[].

Rows with id IS NULL do not pass either of these expressions. To include NULL values additionally:

SELECT * FROM tbl WHERE (id = ANY ('{1, 2}')) IS NOT TRUE; 
vote vote

81

There are two obvious points, as well as the points in the other answer:

  • They are exactly equivalent when using sub queries:

    SELECT * FROM table WHERE column IN(subquery);  SELECT * FROM table WHERE column = ANY(subquery); 

On the other hand:

  • Only the IN operator allows a simple list:

    SELECT * FROM table WHERE column IN(… , … , …); 

Presuming they are exactly the same has caught me out several times when forgetting that ANY doesn’t work with lists.

vote vote

78

vote vote

63

Your goal shouldn't be to add a Z character, it should be to generate a UTC "aware" datetime string in ISO 8601 format. The solution is to pass a UTC timezone object to datetime.now() instead of using datetime.utcnow():

from datetime import datetime, timezone  datetime.now(timezone.utc) >>> datetime.datetime(2020, 1, 8, 6, 6, 24, 260810, tzinfo=datetime.timezone.utc)  datetime.now(timezone.utc).isoformat() >>> '2020-01-08T06:07:04.492045+00:00' 

That looks good, so let's see what Django and dateutil think:

from django.utils.timezone import is_aware is_aware(datetime.now(timezone.utc)) >>> True  from dateutil.parser import isoparse is_aware(isoparse(datetime.now(timezone.utc).isoformat())) >>> True 

Note that you need to use isoparse() from dateutil.parser because the Python documentation for datetime.fromisoformat() says it "does not support parsing arbitrary ISO 8601 strings".

Okay, the Python datetime object and the ISO 8601 string are both UTC "aware". Now let's look at what JavaScript thinks of the datetime string. Borrowing from this answer we get:

let date = '2020-01-08T06:07:04.492045+00:00'; const dateParsed = new Date(Date.parse(date))  document.write(dateParsed); document.write("\n"); // Tue Jan 07 2020 22:07:04 GMT-0800 (Pacific Standard Time)  document.write(dateParsed.toISOString()); document.write("\n"); // 2020-01-08T06:07:04.492Z  document.write(dateParsed.toUTCString()); document.write("\n"); // Wed, 08 Jan 2020 06:07:04 GMT 

Notes:

I approached this problem with a few goals:

  • generate a UTC "aware" datetime string in ISO 8601 format
  • use only Python Standard Library functions for datetime object and string creation
  • validate the datetime object and string with the Django timezone utility function, the dateutil parser and JavaScript functions

Note that this approach does not include a Z suffix and does not use utcnow(). But it's based on the recommendation in the Python documentation and it passes muster with both Django and JavaScript.

See also:

vote vote

60

In Python >= 3.2 you can simply use this:

>>> from datetime import datetime, timezone >>> datetime.now(timezone.utc).isoformat() '2019-03-14T07:55:36.979511+00:00' 

Top 3 video Explaining sql - IN vs ANY operator in PostgreSQL

Related QUESTION?