8000 Added expicit array types, changed how they convert · psqlpy-python/psqlpy@19d1746 · GitHub
[go: up one dir, main page]
Skip to content

Commit 19d1746

Browse files
chandr-andrchandr-andr
committed
Added expicit array types, changed how they convert
Signed-off-by: chandr-andr (Kiselev Aleksandr) <chandr@chandr.net>
1 parent 399cc63 commit 19d1746

File tree

2 files changed

+100
-0
lines changed

2 files changed

+100
-0
lines changed

‎docs/.vuepress/sidebar.ts

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,5 +76,10 @@ export default sidebar({
7676
prefix: "/benchmarks",
7777
link: "/benchmarks.md"
7878
},
79+
{
80+
text: "FAQ",
81+
prefix: "/faq",
82+
link: "/faq.md"
83+
},
7984
],
8085
});

‎docs/faq.md

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,95 @@
1+
---
2+
title: Frequently asked questions
3+
---
4+
5+
Here you can find most common questions and problems.
6+
7+
### LIMIT of OFFSET isn't working
8+
The main problem is PostgreSQL expects `LIMIT` and `OFFSET` to be BIGINT type but when you pass python `int` into `parameters` it converts to `INTEGER`.
9+
10+
#### Problem and Solution:
11+
```python
12+
from psqlpy import ConnectionPool
13+
from psqlpy.extra_types import BigInt
14+
15+
# --- Incorrect ---
16+
async def main() -> None:
17+
pool = ConnectionPool()
18+
await pool.execute(
19+
querystring="SELECT * FROM users LIMIT $1 OFFSET $2",
20+
parameters=[10, 100],
21+
)
22+
23+
24+
# --- Correct ---
25+
async def main() -> None:
26+
pool = ConnectionPool()
27+
await pool.execute(
28+
querystring="SELECT * FROM users LIMIT $1 OFFSET $2",
29+
parameters=[BigInt(10), BigInt(100)],
30+
)
31+
```
32+
33+
### WHERE IN clause isn't working
34+
Instead of using `WHERE <field> IN ()` clause you must use `WHERE <field> = ANY()`.
35+
36+
#### Problem and Solution:
37+
```python
38+
from psqlpy import ConnectionPool
39+
40+
# --- Incorrect ---
41+
async def main() -> None:
42+
pool = ConnectionPool()
43+
await pool.execute(
44+
querystring="SELECT * FROM users WHERE id IN ($1)",
45+
parameters=[
46+
(1, 2, 3),
47+
],
48+
)
49+
50+
51+
# --- Correct ---
52+
async def main() -> None:
53+
pool = ConnectionPool()
54+
await pool.execute(
55+
querystring="SELECT * FROM users WHERE id = ANY($1)",
56+
parameters=[
57+
(1, 2, 3),
58+
],
59+
)
60+
```
61+
62+
### Wrong binary data
63+
64+
Example error: `binary data has array element type 1043 (character varying) instead of expected 25 (text)`.
65+
66+
This exception tells you that you use wrong data type and you need to specify types explicitly.
67+
68+
For example, when we want to make `WHERE` clause with `ANY` and string values, we need to use `TextArray`, see example below:
69+
70+
#### Problem and Solution:
71+
```python
72+
from psqlpy import ConnectionPool
73+
from psqlpy.extra_types import TextArray
74+
75+
# --- Incorrect ---
76+
async def main() -> None:
77+
pool = ConnectionPool()
78+
await pool.execute(
79+
querystring="SELECT * FROM users WHERE name = ANY($1)",
80+
parameters=[
81+
["Foo", "Bar", "Cafe"],
82+
],
83+
)
84+
85+
86+
# --- Correct ---
87+
async def main() -> None:
88+
pool = ConnectionPool()
89+
await pool.execute(
90+
querystring="SELECT * FROM users WHERE name = ANY($1)",
91+
parameters=[
92+
TextArray(["Foo", "Bar", "Cafe"]),
93+
],
94+
)
95+
```

0 commit comments

Comments
 (0)
0