Обсуждение: Query with boolean parameter
Hi all Regarding query and parameter binding in psycopg3: cur.execute('SELECT * FROM system.app_user WHERE can_edit_views = %s', (True,)) <psycopg.Cursor [TUPLES_OK] [INTRANS] (host=localhost port=5433 user=postgres database=test4) at 0x127b7f0> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', (True,)) Traceback (most recent call last): Python Shell, prompt 10, line 1 # Used internally for debug sandbox under external interpreter File "C:\Python310\Lib\site-packages\psycopg\cursor.py", line 555, in execute raise ex.with_traceback(None) psycopg.errors.SyntaxError: syntax error at or near "$1" LINE 1: SELECT * FROM system.app_user WHERE can_edit_views IS $1 ^ Is there any reason why the second query results in a syntax error? I can use the first form but usually to check a boolean or null value in SQL the IS [true|false|null]/ IS NOT [true|false|null] operator is used. -- Paolo De Stefani
> On Mar 18, 2022, at 16:56, Paolo De Stefani <paolo@paolodestefani.it> wrote: > Is there any reason why the second query results in a syntax error? There's not IS operator in PostgreSQL (or in SQL). IS NULL, IS NOT NULL, IS TRUE, and IS FALSE are in effect unary postfixoperators, so you can't construct them that way via parameter substitution.
Il 19/03/2022 01:00 Christophe Pettus ha scritto: >> On Mar 18, 2022, at 16:56, Paolo De Stefani <paolo@paolodestefani.it> >> wrote: >> Is there any reason why the second query results in a syntax error? > > There's not IS operator in PostgreSQL (or in SQL). IS NULL, IS NOT > NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so > you can't construct them that way via parameter substitution. Thanks, i see The problem is (for me) that with psycopg2 this works: cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS TRUE') cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', (True,)) cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', (None,)) Switching to psycopg 3 i have to consider many more differences than i expected -- Paolo De Stefani
> Il 19/03/2022 01:00 Christophe Pettus ha scritto:> >> On Mar 18, 2022, at 16:56, Paolo De Stefani <paolo@paolodestefani.it> > >> wrote: > >> Is there any reason why the second query results in a syntax error? > > > > There's not IS operator in PostgreSQL (or in SQL). IS NULL, IS NOT > > NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so > > you can't construct them that way via parameter substitution. That's unexpected. Thank you for the insight, Christophe. On Sat, 19 Mar 2022 at 13:11, Paolo De Stefani <paolo@paolodestefani.it> wrote: > The problem is (for me) that with psycopg2 this works: > cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS > TRUE') > cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', > (True,)) > cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', > (None,)) > > Switching to psycopg 3 i have to consider many more differences than i > expected Maybe you can play around with IS NOT DISTINCT FROM? https://www.postgresql.org/docs/current/functions-comparison.html In [1]: import psycopg In [2]: cnn = psycopg.connect(autocommit=True) In [5]: cnn.execute("create table dist (id int primary key, cond bool)") In [7]: cnn.cursor().executemany("insert into dist values (%s, %s)", [(1, True), (2, False), (3, None)]) In [9]: cnn.execute("select * from dist where cond is not distinct from %s", [True]).fetchone() Out[9]: (1, True) In [10]: cnn.execute("select * from dist where cond is not distinct from %s", [False]).fetchone() Out[10]: (2, False) In [11]: cnn.execute("select * from dist where cond is not distinct from %s", [None]).fetchone() Out[11]: (3, None) -- Daniele
> On Mar 19, 2022, at 05:10, Paolo De Stefani <paolo@paolodestefani.it> wrote: > Switching to psycopg 3 i have to consider many more differences than i expected There have been some changes in the way psycopg2 does parameter substitution, although that one is an interesting case! You might consider using IS DISTINCT FROM as Daniele suggested, or just =, depending on how you want nulls handled.
On 3/19/22 05:10, Paolo De Stefani wrote: > Il 19/03/2022 01:00 Christophe Pettus ha scritto: >>> On Mar 18, 2022, at 16:56, Paolo De Stefani <paolo@paolodestefani.it> >>> wrote: >>> Is there any reason why the second query results in a syntax error? >> >> There's not IS operator in PostgreSQL (or in SQL). IS NULL, IS NOT >> NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so >> you can't construct them that way via parameter substitution. > > Thanks, i see > > The problem is (for me) that with psycopg2 this works: > cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS TRUE') > cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', > (True,)) > cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', > (None,)) The only way I could get it to work: cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL('TRUE'))) or cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL(str(True)))) cur.fetchone() (False,) cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL('NULL'))) cur.fetchone() (False,) > > Switching to psycopg 3 i have to consider many more differences than i > expected > -- Adrian Klaver adrian.klaver@aklaver.com
Il 19/03/2022 17:35 Christophe Pettus ha scritto: >> On Mar 19, 2022, at 05:10, Paolo De Stefani <paolo@paolodestefani.it> >> wrote: >> Switching to psycopg 3 i have to consider many more differences than i >> expected > > There have been some changes in the way psycopg2 does parameter > substitution, although that one is an interesting case! You might > consider using IS DISTINCT FROM as Daniele suggested, or just =, > depending on how you want nulls handled. thanks to all for the suggestions i thimk i will use the '=' operator what do you mean with 'depending on how you want nulls handled' ??? test4=# create table test (a text, b boolean); CREATE TABLE test4=# insert into test values ('aaa', true), ('bbb', false), ('ccc', null); INSERT 0 3 test4=# select * from test; a | b -----+--- aaa | t bbb | f ccc | (3 rows) test4=# select * from test where b = true; a | b -----+--- aaa | t (1 row) test4=# select * from test where b is true; a | b -----+--- aaa | t (1 row) test4=# select * from test where b = null; a | b ---+--- (0 rows) test4=# select * from test where b is null; a | b -----+--- ccc | (1 row) I will use '= True' or '= False' in psycopg cur.execute with parameter substitution and 'IS NULL' without parameter when i need to check the null value -- Paolo De Stefani