CODEWITHSUNDEEP
pythonsqlpostgresqlcase-sensitiveidentifier
philomachus
philomachus

Reputation: 23

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.

Upvotes: 2

Views: 76

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

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.

Upvotes: 1

Related Questions