Message-ID: <321f190b-d65d-d2f7-d5bb-2ce72aa0b70d@aklaver.com>
Date: Sat, 19 Mar 2022 10:34:32 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Subject: Re: Query with boolean parameter
Content-Language: en-US
To: Paolo De Stefani <paolo@paolodestefani.it>,
 Christophe Pettus <xof@thebuild.com>
Cc: Psycopg <psycopg@postgresql.org>
References: <8df15d1263aaa1a9d36eacb0e5b9a28e@paolodestefani.it>
 <74FC637F-2D25-4CD0-A075-9CB5293FCA5B@thebuild.com>
 <d88ac5f2ae2457bb2b97f98c47cfcdfc@paolodestefani.it>
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <d88ac5f2ae2457bb2b97f98c47cfcdfc@paolodestefani.it>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/321f190b-d65d-d2f7-d5bb-2ce72aa0b70d%40aklaver.com>
Precedence: bulk

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