Search code examples
pythonsqlpostgresqlcase-sensitiveidentifier

case sensitve query returns string instead of list

I have an issue executing a query using python's pgdb using multiple case sensitive columns. The results of most queries return a python list, but if I issue a query against a table specifying multiple case sensitive columns the result is a string.

For example I have a table in a PostgreSQL database with 3 case-sensitve boolean columns named:

(colA, colB, debug)

If I'm interested in selecting more than one column I receive a raw string result from the query:

query = 'SELECT ("colA", debug) FROM my_table;"

or

query = 'SELECT ("colA", "colB") FROM my_table;"

the query will return:

cursor.execute(query)
cursor.fetchone()
['(f,f)']

Issuing the following query:

query = "SELECT * FROM my_table;"
cursor.execute(query)
cursor.fetchone()

results in the expected python list:

[False, False, False]

and if I specify one column in quotes the result is expected:

query = 'SELECT ("colA") FROM my_table;'
cursor.execute(query)
cursor.fetchone()
[False]

I'm hoping someone can point me in the right direction to understand why I receive a raw string when selecting multiple case-sensitve columns. I could issue multiple queries to solve my problem, or just SELECT * but to maintain robust code and protect myself against future changes to the table, I'd prefer to specify my columns.

Solution

  • If you enclose multiple coumns in parentheses you form an ad-hoc row-type, resulting in a single value returned.

    SELECT ("colA", "colB") FROM my_table;

    Drop the parens to get individual columns:

    SELECT "colA", "colB" FROM my_table;

    If in doubt about double-quoting, read the chapter about identifiers in the manual. My standing advice is to use legal, lower-case identifiers only in PostgreSQL.