(psycopg2.errors.UndefinedColumn) column "name" does not exist

Question

I used double quotes on "NAME" to make it case sensitive but it still doesn't work. I get an error saying that the column "name" doesn't exist.

(i use jupyter notebook and sql magic commands)

ppg = [[],[]]
for x in overall_ppg_leaders:
    reg_ppg = %sql SELECT * FROM "rg_player_stats" WHERE "NAME" = :x;
    po_ppg = %sql SELECT * FROM "po_player_stats" WHERE "NAME" = :x;
    ppg[0].append(reg_ppg)
    ppg[1].append(po_ppg)
print(ppg)

I get error:

* postgresql+psycopg2://postgres:***@localhost/NBA_2021-22
(psycopg2.errors.UndefinedColumn) column "name" does not exist
LINE 1: SELECT * FROM rg_player_stats WHERE NAME = 'Kevin Durant';
                                            ^

[SQL: SELECT * FROM rg_player_stats WHERE NAME = %(x)s;]
[parameters: {'x': 'Kevin Durant'}]
(Background on this error at: https://sqlalche.me/e/14/f405)
 * postgresql+psycopg2://postgres:***@localhost/NBA_2021-22
(psycopg2.errors.UndefinedColumn) column "name" does not exist
LINE 1: SELECT * FROM po_player_stats WHERE NAME = 'Kevin Durant';
                                            ^

[SQL: SELECT * FROM po_player_stats WHERE NAME = %(x)s;]
[parameters: {'x': 'Kevin Durant'}]
(Background on this error at: https://sqlalche.me/e/14/f405)
 * postgresql+psycopg2://postgres:***@localhost/NBA_2021-22
(psycopg2.errors.UndefinedColumn) column "name" does not exist
LINE 1: SELECT * FROM rg_player_stats WHERE NAME = 'Donovan Mitchell...
                                            ^

I also noticed that column name with double quotes works when I use cell magic %%sql but gives me an error when I use the single line magic %sql

This code works fine

This code gives me an error

@AKX yes there is, i edited the question you can see the screenshot of the table — RKS2, Oct 04 '22 at 14:45
Don't wrap it in double quotes. Double quotes generally aren't used in SQL, but that can vary from database to database. Stick to using single quotes. Reference here: https://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql — Ivan Sidaruk, Oct 04 '22 at 14:57
@IvanSidaruk. Wrong answer. See [Identifiers](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) where it explains double quotes are used to case preserve identifiers, among other things. — Adrian Klaver, Oct 04 '22 at 14:59
1) Try `...WHERE quote_ident("NAME")`. For more information see [String functions](https://www.postgresql.org/docs/current/functions-string.html). 2) Alternate `...WHERE '"NAME"'`. As in Python `'` or `"` can be used for string quoting. — Adrian Klaver, Oct 04 '22 at 15:02
@AdrianKlaver i tried ```quote_ident()``` it still gives me the same error. and when i use ```'"NAME"'``` i get empty results. — RKS2, Oct 04 '22 at 15:09
@AdrianKlaver I solved this issue by using ```"\"NAME\""``` Thank you for your help :) — RKS2, Oct 04 '22 at 15:12
@AdrianKlaver ```'"NAME"'``` didn't give me an error but it gave me empty results. I don't understand why it didn't work — RKS2, Oct 04 '22 at 15:18
I should have tested first, I was wrong on quote_ident() being useful in this case. Did '"NAME"' not work? As it should have done the same thing as "\"NAME\"" with the ' escaping the ". When I do `"\"NAME\"" it turns into '"NAME"'. — Adrian Klaver, Oct 04 '22 at 15:18

score 0 · Accepted Answer · answered Oct 04 '22 at 15:14

When using single line magic command %sql, use "\"NAME\"" instead of "NAME" so that it is case sensitive.

ppg = [[],[]]
for x in overall_ppg_leaders:
    reg_ppg = %sql SELECT "\"PPG\"" FROM "rg_player_stats" WHERE "\"NAME\"" = :x;
    po_ppg = %sql SELECT "\"PPG\"" FROM "po_player_stats" WHERE "\"NAME\"" = :x;
    ppg[0].append(reg_ppg)
    ppg[1].append(po_ppg)
print(ppg)

This code WORKS

(psycopg2.errors.UndefinedColumn) column "name" does not exist

1 Answers1