英文:
DuckDB Binder Error: Referenced column not found in FROM clause
问题
我在DuckDB中处理从json中读取的数据库。这是json：```json[{"account": "abcde","data": [{"name": "hey","amount": 1,"flow": "INFLOW"},{"name": "hello","amount": -2,"flow": null}]},{"account": "hijkl","data": [{"name": "bonjour","amount": 1,"flow": "INFLOW"},{"name": "hallo","amount": -3,"flow": "OUTFLOW"}]}]
我在Python中这样打开它：
import duckdbduckdb.sql("""CREATE OR REPLACE TABLE mytable AS SELECT * FROM "example2.json"""")
这一切都很顺利，我得到了我的表的副本，但是然后我尝试更新它：
duckdb.sql("""UPDATE mytable SET data = NULL WHERE account = "abcde"""")
这导致崩溃：
---------------------------------------------------------------------------BinderException Traceback (most recent call last)Cell In[109], line 1----> 1 duckdb.sql("""2 UPDATE mytable SET data = NULL WHERE account = "abcde"3 """)6 # duckdb.sql("""7 # DELETE FROM mytable WHERE account = "abcde"8 # """)10 duckdb.sql("""11 SELECT * FROM mytable12 """)BinderException: Binder Error: Referenced column "abcde" not found in FROM clause!Candidate bindings: "mytable.data"LINE 2: ...mytable SET data = NULL WHERE account = "abcde"^
我已经搜索了文档和错误，但我就是找不到我在这里做错了什么。
<details><summary>英文:</summary>I am working in DuckDB in a database that I read from json.Here is the json:```json[{"account": "abcde","data": [{"name": "hey","amount":1,"flow":"INFLOW"},{"name": "hello","amount":-2,"flow": null}]},{"account": "hijkl","data": [{"name": "bonjour","amount":1,"flow":"INFLOW"},{"name": "hallo","amount":-3,"flow":"OUTFLOW"}]}]
I am opening it in Python as follows:
import duckdbduckdb.sql("""CREATE OR REPLACE TABLE mytable AS SELECT * FROM "example2.json"""")
This all works fine and I get a copy of my table, but then I try to update it:
duckdb.sql("""UPDATE mytable SET data = NULL WHERE account = "abcde"""")
which crashes with
---------------------------------------------------------------------------BinderException Traceback (most recent call last)Cell In[109], line 1----> 1 duckdb.sql("""2 UPDATE mytable SET data = NULL WHERE account = "abcde"3 """)6 # duckdb.sql("""7 # DELETE FROM mytable WHERE account = "abcde"8 # """)10 duckdb.sql("""11 SELECT * FROM mytable12 """)BinderException: Binder Error: Referenced column "abcde" not found in FROM clause!Candidate bindings: "mytable.data"LINE 2: ...mytable SET data = NULL WHERE account = "abcde"^
I have searched the documentation and the error but I just can't find what I am doing wrong here.
答案1
得分: 1
I actually solved the issue. I had to use single quotes ' instead of double quotes " in the string comparison...
Solution
duckdb.sql('''UPDATE mytable SET data = NULL WHERE account = 'abcde'''')
correctly does
┌─────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┐│ account │ data ││ varchar │ struct("name" varchar, amount bigint, flow varchar)[] │├─────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┤│ hijkl │ [{'name': bonjour, 'amount': 1, 'flow': INFLOW}, {'name': hallo, 'amount': -3, 'flow': OUTFLOW}] ││ abcde │ NULL │└─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘
Interestingly, ChatGPT helped me spot this mistake. (There is a ban on posting AI answers, but it's OK if they are human-verified).
英文:
I actually solved the issue. I had to use single quotes ' instead of double quotes " in the string comparison...
Solution
duckdb.sql("""UPDATE mytable SET data = NULL WHERE account = 'abcde'""")
correctly does
┌─────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┐│ account │ data ││ varchar │ struct("name" varchar, amount bigint, flow varchar)[] │├─────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┤│ hijkl │ [{'name': bonjour, 'amount': 1, 'flow': INFLOW}, {'name': hallo, 'amount': -3, 'flow': OUTFLOW}] ││ abcde │ NULL │└─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘
Interestingly, ChatGPT helped me spot this mistake. (There is a ban on posting AI answers, but it's OK if they are human-verified).
答案2
得分: 0
在处理 DuckDB 中的 varchar 时，请使用单引号 '' 而不是双引号 ""。
英文:
When dealing with varchar in DuckDB, use single quotes '' instead of double quotes "".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论