英文:
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 duckdb
duckdb.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 mytable
12 """)
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 duckdb
duckdb.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 mytable
12 """)
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 "".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论