Search code examples
sqlpostgresqlfedorapsycopg2

Use columns that are always NULL, never NULL, and sometimes NULL in the same SELECT statement

I'm using Python 3.6, PostgreSQL 9.6, and Psycopg2 2.6.2 on a Fedora 27 64-bit system.

I have a SELECT query called my_query that pulls data from about 15 columns in one table. Several of the columns may be NULL. It's written as

SELECT col_01, col_02, ... col_15  
FROM my_table  
WHERE (col_01, col_02, ... col_15) = (%s, %s, ... %s)

The data, called my_data, is written in a tuple as 

(value_01, value_02, ... value_15)

When I run

cursor.execute(my_query, my_data)
cursor_result = cursor.fetchone()

cursor_result becomes None.

Assuming the problem was with the nullable columns, I removed them from my query and my data and cursor_result was no longer None.

I also tried a query with only nullable columns; it produced the same result as the original query: cursor_result became None.

My question: is there a way around my problem? I want to be able to SELECT columns that are never NULL, always NULL, or sometimes NULL.

Thank you in advance for your help.

Solution

  • The ANSI 99 standard for SQL introduced the IS [NOT] DISTINCT FROM operator, which behaves identically to the equality comparisons involving non-null values but treats all NULLs as being the same (NOT DISTINCT, given the curious negative phrasing).

    Most database products do not yet support this operator, so far as I'm aware, but luckily PostgreSQL does