1 package pgx_test
2
3 import (
4 "bytes"
5 "context"
6 "net"
7 "os"
8 "reflect"
9 "strings"
10 "testing"
11 "time"
12
13 "github.com/jackc/pgx/v5"
14 "github.com/jackc/pgx/v5/pgxtest"
15 "github.com/stretchr/testify/assert"
16 "github.com/stretchr/testify/require"
17 )
18
19 func TestDateTranscode(t *testing.T) {
20 t.Parallel()
21
22 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
23 defer cancel()
24
25 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
26 dates := []time.Time{
27 time.Date(1, 1, 1, 0, 0, 0, 0, time.UTC),
28 time.Date(1000, 1, 1, 0, 0, 0, 0, time.UTC),
29 time.Date(1600, 1, 1, 0, 0, 0, 0, time.UTC),
30 time.Date(1700, 1, 1, 0, 0, 0, 0, time.UTC),
31 time.Date(1800, 1, 1, 0, 0, 0, 0, time.UTC),
32 time.Date(1900, 1, 1, 0, 0, 0, 0, time.UTC),
33 time.Date(1990, 1, 1, 0, 0, 0, 0, time.UTC),
34 time.Date(1999, 12, 31, 0, 0, 0, 0, time.UTC),
35 time.Date(2000, 1, 1, 0, 0, 0, 0, time.UTC),
36 time.Date(2001, 1, 2, 0, 0, 0, 0, time.UTC),
37 time.Date(2004, 2, 29, 0, 0, 0, 0, time.UTC),
38 time.Date(2013, 7, 4, 0, 0, 0, 0, time.UTC),
39 time.Date(2013, 12, 25, 0, 0, 0, 0, time.UTC),
40 time.Date(2029, 1, 1, 0, 0, 0, 0, time.UTC),
41 time.Date(2081, 1, 1, 0, 0, 0, 0, time.UTC),
42 time.Date(2096, 2, 29, 0, 0, 0, 0, time.UTC),
43 time.Date(2550, 1, 1, 0, 0, 0, 0, time.UTC),
44 time.Date(9999, 12, 31, 0, 0, 0, 0, time.UTC),
45 }
46
47 for _, actualDate := range dates {
48 var d time.Time
49
50 err := conn.QueryRow(context.Background(), "select $1::date", actualDate).Scan(&d)
51 if err != nil {
52 t.Fatalf("Unexpected failure on QueryRow Scan: %v", err)
53 }
54 if !actualDate.Equal(d) {
55 t.Errorf("Did not transcode date successfully: %v is not %v", d, actualDate)
56 }
57 }
58 })
59 }
60
61 func TestTimestampTzTranscode(t *testing.T) {
62 t.Parallel()
63
64 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
65 defer cancel()
66
67 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
68 inputTime := time.Date(2013, 1, 2, 3, 4, 5, 6000, time.Local)
69
70 var outputTime time.Time
71
72 err := conn.QueryRow(context.Background(), "select $1::timestamptz", inputTime).Scan(&outputTime)
73 if err != nil {
74 t.Fatalf("QueryRow Scan failed: %v", err)
75 }
76 if !inputTime.Equal(outputTime) {
77 t.Errorf("Did not transcode time successfully: %v is not %v", outputTime, inputTime)
78 }
79 })
80 }
81
82
83
84 func TestJSONAndJSONBTranscode(t *testing.T) {
85 t.Parallel()
86
87 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
88 defer cancel()
89
90 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
91 for _, typename := range []string{"json", "jsonb"} {
92 if _, ok := conn.TypeMap().TypeForName(typename); !ok {
93 continue
94 }
95
96 testJSONString(t, conn, typename)
97 testJSONStringPointer(t, conn, typename)
98 }
99 })
100 }
101
102 func TestJSONAndJSONBTranscodeExtendedOnly(t *testing.T) {
103 t.Parallel()
104
105 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
106 defer closeConn(t, conn)
107
108 for _, typename := range []string{"json", "jsonb"} {
109 if _, ok := conn.TypeMap().TypeForName(typename); !ok {
110 continue
111 }
112 testJSONSingleLevelStringMap(t, conn, typename)
113 testJSONNestedMap(t, conn, typename)
114 testJSONStringArray(t, conn, typename)
115 testJSONInt64Array(t, conn, typename)
116 testJSONInt16ArrayFailureDueToOverflow(t, conn, typename)
117 testJSONStruct(t, conn, typename)
118 }
119
120 }
121
122 func testJSONString(t testing.TB, conn *pgx.Conn, typename string) {
123 input := `{"key": "value"}`
124 expectedOutput := map[string]string{"key": "value"}
125 var output map[string]string
126 err := conn.QueryRow(context.Background(), "select $1::"+typename, input).Scan(&output)
127 if err != nil {
128 t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
129 return
130 }
131
132 if !reflect.DeepEqual(expectedOutput, output) {
133 t.Errorf("%s: Did not transcode map[string]string successfully: %v is not %v", typename, expectedOutput, output)
134 return
135 }
136 }
137
138 func testJSONStringPointer(t testing.TB, conn *pgx.Conn, typename string) {
139 input := `{"key": "value"}`
140 expectedOutput := map[string]string{"key": "value"}
141 var output map[string]string
142 err := conn.QueryRow(context.Background(), "select $1::"+typename, &input).Scan(&output)
143 if err != nil {
144 t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
145 return
146 }
147
148 if !reflect.DeepEqual(expectedOutput, output) {
149 t.Errorf("%s: Did not transcode map[string]string successfully: %v is not %v", typename, expectedOutput, output)
150 return
151 }
152 }
153
154 func testJSONSingleLevelStringMap(t *testing.T, conn *pgx.Conn, typename string) {
155 input := map[string]string{"key": "value"}
156 var output map[string]string
157 err := conn.QueryRow(context.Background(), "select $1::"+typename, input).Scan(&output)
158 if err != nil {
159 t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
160 return
161 }
162
163 if !reflect.DeepEqual(input, output) {
164 t.Errorf("%s: Did not transcode map[string]string successfully: %v is not %v", typename, input, output)
165 return
166 }
167 }
168
169 func testJSONNestedMap(t *testing.T, conn *pgx.Conn, typename string) {
170 input := map[string]any{
171 "name": "Uncanny",
172 "stats": map[string]any{"hp": float64(107), "maxhp": float64(150)},
173 "inventory": []any{"phone", "key"},
174 }
175 var output map[string]any
176 err := conn.QueryRow(context.Background(), "select $1::"+typename, input).Scan(&output)
177 if err != nil {
178 t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
179 return
180 }
181
182 if !reflect.DeepEqual(input, output) {
183 t.Errorf("%s: Did not transcode map[string]any successfully: %v is not %v", typename, input, output)
184 return
185 }
186 }
187
188 func testJSONStringArray(t *testing.T, conn *pgx.Conn, typename string) {
189 input := []string{"foo", "bar", "baz"}
190 var output []string
191 err := conn.QueryRow(context.Background(), "select $1::"+typename, input).Scan(&output)
192 if err != nil {
193 t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
194 }
195
196 if !reflect.DeepEqual(input, output) {
197 t.Errorf("%s: Did not transcode []string successfully: %v is not %v", typename, input, output)
198 }
199 }
200
201 func testJSONInt64Array(t *testing.T, conn *pgx.Conn, typename string) {
202 input := []int64{1, 2, 234432}
203 var output []int64
204 err := conn.QueryRow(context.Background(), "select $1::"+typename, input).Scan(&output)
205 if err != nil {
206 t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
207 }
208
209 if !reflect.DeepEqual(input, output) {
210 t.Errorf("%s: Did not transcode []int64 successfully: %v is not %v", typename, input, output)
211 }
212 }
213
214 func testJSONInt16ArrayFailureDueToOverflow(t *testing.T, conn *pgx.Conn, typename string) {
215 input := []int{1, 2, 234432}
216 var output []int16
217 err := conn.QueryRow(context.Background(), "select $1::"+typename, input).Scan(&output)
218 if err == nil || err.Error() != "can't scan into dest[0]: json: cannot unmarshal number 234432 into Go value of type int16" {
219 t.Errorf("%s: Expected *json.UnmarkalTypeError, but got %v", typename, err)
220 }
221 }
222
223 func testJSONStruct(t *testing.T, conn *pgx.Conn, typename string) {
224 type person struct {
225 Name string `json:"name"`
226 Age int `json:"age"`
227 }
228
229 input := person{
230 Name: "John",
231 Age: 42,
232 }
233
234 var output person
235
236 err := conn.QueryRow(context.Background(), "select $1::"+typename, input).Scan(&output)
237 if err != nil {
238 t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
239 }
240
241 if !reflect.DeepEqual(input, output) {
242 t.Errorf("%s: Did not transcode struct successfully: %v is not %v", typename, input, output)
243 }
244 }
245
246 func mustParseCIDR(t testing.TB, s string) *net.IPNet {
247 _, ipnet, err := net.ParseCIDR(s)
248 if err != nil {
249 t.Fatal(err)
250 }
251
252 return ipnet
253 }
254
255 func TestInetCIDRTranscodeIPNet(t *testing.T) {
256 t.Parallel()
257
258 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
259 defer cancel()
260
261 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
262 tests := []struct {
263 sql string
264 value *net.IPNet
265 }{
266 {"select $1::inet", mustParseCIDR(t, "0.0.0.0/32")},
267 {"select $1::inet", mustParseCIDR(t, "127.0.0.1/32")},
268 {"select $1::inet", mustParseCIDR(t, "12.34.56.0/32")},
269 {"select $1::inet", mustParseCIDR(t, "192.168.1.0/24")},
270 {"select $1::inet", mustParseCIDR(t, "255.0.0.0/8")},
271 {"select $1::inet", mustParseCIDR(t, "255.255.255.255/32")},
272 {"select $1::inet", mustParseCIDR(t, "::/128")},
273 {"select $1::inet", mustParseCIDR(t, "::/0")},
274 {"select $1::inet", mustParseCIDR(t, "::1/128")},
275 {"select $1::inet", mustParseCIDR(t, "2607:f8b0:4009:80b::200e/128")},
276 {"select $1::cidr", mustParseCIDR(t, "0.0.0.0/32")},
277 {"select $1::cidr", mustParseCIDR(t, "127.0.0.1/32")},
278 {"select $1::cidr", mustParseCIDR(t, "12.34.56.0/32")},
279 {"select $1::cidr", mustParseCIDR(t, "192.168.1.0/24")},
280 {"select $1::cidr", mustParseCIDR(t, "255.0.0.0/8")},
281 {"select $1::cidr", mustParseCIDR(t, "255.255.255.255/32")},
282 {"select $1::cidr", mustParseCIDR(t, "::/128")},
283 {"select $1::cidr", mustParseCIDR(t, "::/0")},
284 {"select $1::cidr", mustParseCIDR(t, "::1/128")},
285 {"select $1::cidr", mustParseCIDR(t, "2607:f8b0:4009:80b::200e/128")},
286 }
287
288 for i, tt := range tests {
289 if conn.PgConn().ParameterStatus("crdb_version") != "" && strings.Contains(tt.sql, "cidr") {
290 t.Log("Server does not support cidr type (https://github.com/cockroachdb/cockroach/issues/18846)")
291 continue
292 }
293
294 var actual net.IPNet
295
296 err := conn.QueryRow(context.Background(), tt.sql, tt.value).Scan(&actual)
297 if err != nil {
298 t.Errorf("%d. Unexpected failure: %v (sql -> %v, value -> %v)", i, err, tt.sql, tt.value)
299 continue
300 }
301
302 if actual.String() != tt.value.String() {
303 t.Errorf("%d. Expected %v, got %v (sql -> %v)", i, tt.value, actual, tt.sql)
304 }
305 }
306 })
307 }
308
309 func TestInetCIDRTranscodeIP(t *testing.T) {
310 t.Parallel()
311
312 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
313 defer cancel()
314
315 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
316 tests := []struct {
317 sql string
318 value net.IP
319 }{
320 {"select $1::inet", net.ParseIP("0.0.0.0")},
321 {"select $1::inet", net.ParseIP("127.0.0.1")},
322 {"select $1::inet", net.ParseIP("12.34.56.0")},
323 {"select $1::inet", net.ParseIP("255.255.255.255")},
324 {"select $1::inet", net.ParseIP("::1")},
325 {"select $1::inet", net.ParseIP("2607:f8b0:4009:80b::200e")},
326 {"select $1::cidr", net.ParseIP("0.0.0.0")},
327 {"select $1::cidr", net.ParseIP("127.0.0.1")},
328 {"select $1::cidr", net.ParseIP("12.34.56.0")},
329 {"select $1::cidr", net.ParseIP("255.255.255.255")},
330 {"select $1::cidr", net.ParseIP("::1")},
331 {"select $1::cidr", net.ParseIP("2607:f8b0:4009:80b::200e")},
332 }
333
334 for i, tt := range tests {
335 if conn.PgConn().ParameterStatus("crdb_version") != "" && strings.Contains(tt.sql, "cidr") {
336 t.Log("Server does not support cidr type (https://github.com/cockroachdb/cockroach/issues/18846)")
337 continue
338 }
339
340 var actual net.IP
341
342 err := conn.QueryRow(context.Background(), tt.sql, tt.value).Scan(&actual)
343 if err != nil {
344 t.Errorf("%d. Unexpected failure: %v (sql -> %v, value -> %v)", i, err, tt.sql, tt.value)
345 continue
346 }
347
348 if !actual.Equal(tt.value) {
349 t.Errorf("%d. Expected %v, got %v (sql -> %v)", i, tt.value, actual, tt.sql)
350 }
351
352 ensureConnValid(t, conn)
353 }
354
355 failTests := []struct {
356 sql string
357 value *net.IPNet
358 }{
359 {"select $1::inet", mustParseCIDR(t, "192.168.1.0/24")},
360 {"select $1::cidr", mustParseCIDR(t, "192.168.1.0/24")},
361 }
362 for i, tt := range failTests {
363 var actual net.IP
364
365 err := conn.QueryRow(context.Background(), tt.sql, tt.value).Scan(&actual)
366 if err == nil {
367 t.Errorf("%d. Expected failure but got none", i)
368 continue
369 }
370
371 ensureConnValid(t, conn)
372 }
373 })
374 }
375
376 func TestInetCIDRArrayTranscodeIPNet(t *testing.T) {
377 t.Parallel()
378
379 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
380 defer cancel()
381
382 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
383 tests := []struct {
384 sql string
385 value []*net.IPNet
386 }{
387 {
388 "select $1::inet[]",
389 []*net.IPNet{
390 mustParseCIDR(t, "0.0.0.0/32"),
391 mustParseCIDR(t, "127.0.0.1/32"),
392 mustParseCIDR(t, "12.34.56.0/32"),
393 mustParseCIDR(t, "192.168.1.0/24"),
394 mustParseCIDR(t, "255.0.0.0/8"),
395 mustParseCIDR(t, "255.255.255.255/32"),
396 mustParseCIDR(t, "::/128"),
397 mustParseCIDR(t, "::/0"),
398 mustParseCIDR(t, "::1/128"),
399 mustParseCIDR(t, "2607:f8b0:4009:80b::200e/128"),
400 },
401 },
402 {
403 "select $1::cidr[]",
404 []*net.IPNet{
405 mustParseCIDR(t, "0.0.0.0/32"),
406 mustParseCIDR(t, "127.0.0.1/32"),
407 mustParseCIDR(t, "12.34.56.0/32"),
408 mustParseCIDR(t, "192.168.1.0/24"),
409 mustParseCIDR(t, "255.0.0.0/8"),
410 mustParseCIDR(t, "255.255.255.255/32"),
411 mustParseCIDR(t, "::/128"),
412 mustParseCIDR(t, "::/0"),
413 mustParseCIDR(t, "::1/128"),
414 mustParseCIDR(t, "2607:f8b0:4009:80b::200e/128"),
415 },
416 },
417 }
418
419 for i, tt := range tests {
420 if conn.PgConn().ParameterStatus("crdb_version") != "" && strings.Contains(tt.sql, "cidr") {
421 t.Log("Server does not support cidr type (https://github.com/cockroachdb/cockroach/issues/18846)")
422 continue
423 }
424
425 var actual []*net.IPNet
426
427 err := conn.QueryRow(context.Background(), tt.sql, tt.value).Scan(&actual)
428 if err != nil {
429 t.Errorf("%d. Unexpected failure: %v (sql -> %v, value -> %v)", i, err, tt.sql, tt.value)
430 continue
431 }
432
433 if !reflect.DeepEqual(actual, tt.value) {
434 t.Errorf("%d. Expected %v, got %v (sql -> %v)", i, tt.value, actual, tt.sql)
435 }
436
437 ensureConnValid(t, conn)
438 }
439 })
440 }
441
442 func TestInetCIDRArrayTranscodeIP(t *testing.T) {
443 t.Parallel()
444
445 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
446 defer cancel()
447
448 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
449 tests := []struct {
450 sql string
451 value []net.IP
452 }{
453 {
454 "select $1::inet[]",
455 []net.IP{
456 net.ParseIP("0.0.0.0"),
457 net.ParseIP("127.0.0.1"),
458 net.ParseIP("12.34.56.0"),
459 net.ParseIP("255.255.255.255"),
460 net.ParseIP("2607:f8b0:4009:80b::200e"),
461 },
462 },
463 {
464 "select $1::cidr[]",
465 []net.IP{
466 net.ParseIP("0.0.0.0"),
467 net.ParseIP("127.0.0.1"),
468 net.ParseIP("12.34.56.0"),
469 net.ParseIP("255.255.255.255"),
470 net.ParseIP("2607:f8b0:4009:80b::200e"),
471 },
472 },
473 }
474
475 for i, tt := range tests {
476 if conn.PgConn().ParameterStatus("crdb_version") != "" && strings.Contains(tt.sql, "cidr") {
477 t.Log("Server does not support cidr type (https://github.com/cockroachdb/cockroach/issues/18846)")
478 continue
479 }
480
481 var actual []net.IP
482
483 err := conn.QueryRow(context.Background(), tt.sql, tt.value).Scan(&actual)
484 if err != nil {
485 t.Errorf("%d. Unexpected failure: %v (sql -> %v, value -> %v)", i, err, tt.sql, tt.value)
486 continue
487 }
488
489 assert.Equal(t, len(tt.value), len(actual), "%d", i)
490 for j := range actual {
491 assert.True(t, actual[j].Equal(tt.value[j]), "%d", i)
492 }
493
494 ensureConnValid(t, conn)
495 }
496
497 failTests := []struct {
498 sql string
499 value []*net.IPNet
500 }{
501 {
502 "select $1::inet[]",
503 []*net.IPNet{
504 mustParseCIDR(t, "12.34.56.0/32"),
505 mustParseCIDR(t, "192.168.1.0/24"),
506 },
507 },
508 {
509 "select $1::cidr[]",
510 []*net.IPNet{
511 mustParseCIDR(t, "12.34.56.0/32"),
512 mustParseCIDR(t, "192.168.1.0/24"),
513 },
514 },
515 }
516
517 for i, tt := range failTests {
518 var actual []net.IP
519
520 err := conn.QueryRow(context.Background(), tt.sql, tt.value).Scan(&actual)
521 if err == nil {
522 t.Errorf("%d. Expected failure but got none", i)
523 continue
524 }
525
526 ensureConnValid(t, conn)
527 }
528 })
529 }
530
531 func TestInetCIDRTranscodeWithJustIP(t *testing.T) {
532 t.Parallel()
533
534 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
535 defer cancel()
536
537 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
538 tests := []struct {
539 sql string
540 value string
541 }{
542 {"select $1::inet", "0.0.0.0/32"},
543 {"select $1::inet", "127.0.0.1/32"},
544 {"select $1::inet", "12.34.56.0/32"},
545 {"select $1::inet", "255.255.255.255/32"},
546 {"select $1::inet", "::/128"},
547 {"select $1::inet", "2607:f8b0:4009:80b::200e/128"},
548 {"select $1::cidr", "0.0.0.0/32"},
549 {"select $1::cidr", "127.0.0.1/32"},
550 {"select $1::cidr", "12.34.56.0/32"},
551 {"select $1::cidr", "255.255.255.255/32"},
552 {"select $1::cidr", "::/128"},
553 {"select $1::cidr", "2607:f8b0:4009:80b::200e/128"},
554 }
555
556 for i, tt := range tests {
557 if conn.PgConn().ParameterStatus("crdb_version") != "" && strings.Contains(tt.sql, "cidr") {
558 t.Log("Server does not support cidr type (https://github.com/cockroachdb/cockroach/issues/18846)")
559 continue
560 }
561
562 expected := mustParseCIDR(t, tt.value)
563 var actual net.IPNet
564
565 err := conn.QueryRow(context.Background(), tt.sql, expected.IP).Scan(&actual)
566 if err != nil {
567 t.Errorf("%d. Unexpected failure: %v (sql -> %v, value -> %v)", i, err, tt.sql, tt.value)
568 continue
569 }
570
571 if actual.String() != expected.String() {
572 t.Errorf("%d. Expected %v, got %v (sql -> %v)", i, tt.value, actual, tt.sql)
573 }
574
575 ensureConnValid(t, conn)
576 }
577 })
578 }
579
580 func TestArrayDecoding(t *testing.T) {
581 t.Parallel()
582
583 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
584 defer cancel()
585
586 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
587 tests := []struct {
588 sql string
589 query any
590 scan any
591 assert func(testing.TB, any, any)
592 }{
593 {
594 "select $1::bool[]", []bool{true, false, true}, &[]bool{},
595 func(t testing.TB, query, scan any) {
596 if !reflect.DeepEqual(query, *(scan.(*[]bool))) {
597 t.Errorf("failed to encode bool[]")
598 }
599 },
600 },
601 {
602 "select $1::smallint[]", []int16{2, 4, 484, 32767}, &[]int16{},
603 func(t testing.TB, query, scan any) {
604 if !reflect.DeepEqual(query, *(scan.(*[]int16))) {
605 t.Errorf("failed to encode smallint[]")
606 }
607 },
608 },
609 {
610 "select $1::smallint[]", []uint16{2, 4, 484, 32767}, &[]uint16{},
611 func(t testing.TB, query, scan any) {
612 if !reflect.DeepEqual(query, *(scan.(*[]uint16))) {
613 t.Errorf("failed to encode smallint[]")
614 }
615 },
616 },
617 {
618 "select $1::int[]", []int32{2, 4, 484}, &[]int32{},
619 func(t testing.TB, query, scan any) {
620 if !reflect.DeepEqual(query, *(scan.(*[]int32))) {
621 t.Errorf("failed to encode int[]")
622 }
623 },
624 },
625 {
626 "select $1::int[]", []uint32{2, 4, 484, 2147483647}, &[]uint32{},
627 func(t testing.TB, query, scan any) {
628 if !reflect.DeepEqual(query, *(scan.(*[]uint32))) {
629 t.Errorf("failed to encode int[]")
630 }
631 },
632 },
633 {
634 "select $1::bigint[]", []int64{2, 4, 484, 9223372036854775807}, &[]int64{},
635 func(t testing.TB, query, scan any) {
636 if !reflect.DeepEqual(query, *(scan.(*[]int64))) {
637 t.Errorf("failed to encode bigint[]")
638 }
639 },
640 },
641 {
642 "select $1::bigint[]", []uint64{2, 4, 484, 9223372036854775807}, &[]uint64{},
643 func(t testing.TB, query, scan any) {
644 if !reflect.DeepEqual(query, *(scan.(*[]uint64))) {
645 t.Errorf("failed to encode bigint[]")
646 }
647 },
648 },
649 {
650 "select $1::text[]", []string{"it's", "over", "9000!"}, &[]string{},
651 func(t testing.TB, query, scan any) {
652 if !reflect.DeepEqual(query, *(scan.(*[]string))) {
653 t.Errorf("failed to encode text[]")
654 }
655 },
656 },
657 {
658 "select $1::timestamptz[]", []time.Time{time.Unix(323232, 0), time.Unix(3239949334, 00)}, &[]time.Time{},
659 func(t testing.TB, query, scan any) {
660 queryTimeSlice := query.([]time.Time)
661 scanTimeSlice := *(scan.(*[]time.Time))
662 require.Equal(t, len(queryTimeSlice), len(scanTimeSlice))
663 for i := range queryTimeSlice {
664 assert.Truef(t, queryTimeSlice[i].Equal(scanTimeSlice[i]), "%d", i)
665 }
666 },
667 },
668 {
669 "select $1::bytea[]", [][]byte{{0, 1, 2, 3}, {4, 5, 6, 7}}, &[][]byte{},
670 func(t testing.TB, query, scan any) {
671 queryBytesSliceSlice := query.([][]byte)
672 scanBytesSliceSlice := *(scan.(*[][]byte))
673 if len(queryBytesSliceSlice) != len(scanBytesSliceSlice) {
674 t.Errorf("failed to encode byte[][] to bytea[]: expected %d to equal %d", len(queryBytesSliceSlice), len(scanBytesSliceSlice))
675 }
676 for i := range queryBytesSliceSlice {
677 qb := queryBytesSliceSlice[i]
678 sb := scanBytesSliceSlice[i]
679 if !bytes.Equal(qb, sb) {
680 t.Errorf("failed to encode byte[][] to bytea[]: expected %v to equal %v", qb, sb)
681 }
682 }
683 },
684 },
685 }
686
687 for i, tt := range tests {
688 err := conn.QueryRow(context.Background(), tt.sql, tt.query).Scan(tt.scan)
689 if err != nil {
690 t.Errorf(`%d. error reading array: %v`, i, err)
691 continue
692 }
693 tt.assert(t, tt.query, tt.scan)
694 ensureConnValid(t, conn)
695 }
696 })
697 }
698
699 func TestEmptyArrayDecoding(t *testing.T) {
700 t.Parallel()
701
702 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
703 defer cancel()
704
705 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
706 var val []string
707
708 err := conn.QueryRow(context.Background(), "select array[]::text[]").Scan(&val)
709 if err != nil {
710 t.Errorf(`error reading array: %v`, err)
711 }
712 if len(val) != 0 {
713 t.Errorf("Expected 0 values, got %d", len(val))
714 }
715
716 var n, m int32
717
718 err = conn.QueryRow(context.Background(), "select 1::integer, array[]::text[], 42::integer").Scan(&n, &val, &m)
719 if err != nil {
720 t.Errorf(`error reading array: %v`, err)
721 }
722 if len(val) != 0 {
723 t.Errorf("Expected 0 values, got %d", len(val))
724 }
725 if n != 1 {
726 t.Errorf("Expected n to be 1, but it was %d", n)
727 }
728 if m != 42 {
729 t.Errorf("Expected n to be 42, but it was %d", n)
730 }
731
732 rows, err := conn.Query(context.Background(), "select 1::integer, array['test']::text[] union select 2::integer, array[]::text[] union select 3::integer, array['test']::text[]")
733 if err != nil {
734 t.Errorf(`error retrieving rows with array: %v`, err)
735 }
736 defer rows.Close()
737
738 for rows.Next() {
739 err = rows.Scan(&n, &val)
740 if err != nil {
741 t.Errorf(`error reading array: %v`, err)
742 }
743 }
744 })
745 }
746
747 func TestPointerPointer(t *testing.T) {
748 t.Parallel()
749
750 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
751 defer cancel()
752
753 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
754 pgxtest.SkipCockroachDB(t, conn, "Server auto converts ints to bigint and test relies on exact types")
755
756 type allTypes struct {
757 s *string
758 i16 *int16
759 i32 *int32
760 i64 *int64
761 f32 *float32
762 f64 *float64
763 b *bool
764 t *time.Time
765 }
766
767 var actual, zero, expected allTypes
768
769 {
770 s := "foo"
771 expected.s = &s
772 i16 := int16(1)
773 expected.i16 = &i16
774 i32 := int32(1)
775 expected.i32 = &i32
776 i64 := int64(1)
777 expected.i64 = &i64
778 f32 := float32(1.23)
779 expected.f32 = &f32
780 f64 := float64(1.23)
781 expected.f64 = &f64
782 b := true
783 expected.b = &b
784 t := time.Unix(123, 5000)
785 expected.t = &t
786 }
787
788 tests := []struct {
789 sql string
790 queryArgs []any
791 scanArgs []any
792 expected allTypes
793 }{
794 {"select $1::text", []any{expected.s}, []any{&actual.s}, allTypes{s: expected.s}},
795 {"select $1::text", []any{zero.s}, []any{&actual.s}, allTypes{}},
796 {"select $1::int2", []any{expected.i16}, []any{&actual.i16}, allTypes{i16: expected.i16}},
797 {"select $1::int2", []any{zero.i16}, []any{&actual.i16}, allTypes{}},
798 {"select $1::int4", []any{expected.i32}, []any{&actual.i32}, allTypes{i32: expected.i32}},
799 {"select $1::int4", []any{zero.i32}, []any{&actual.i32}, allTypes{}},
800 {"select $1::int8", []any{expected.i64}, []any{&actual.i64}, allTypes{i64: expected.i64}},
801 {"select $1::int8", []any{zero.i64}, []any{&actual.i64}, allTypes{}},
802 {"select $1::float4", []any{expected.f32}, []any{&actual.f32}, allTypes{f32: expected.f32}},
803 {"select $1::float4", []any{zero.f32}, []any{&actual.f32}, allTypes{}},
804 {"select $1::float8", []any{expected.f64}, []any{&actual.f64}, allTypes{f64: expected.f64}},
805 {"select $1::float8", []any{zero.f64}, []any{&actual.f64}, allTypes{}},
806 {"select $1::bool", []any{expected.b}, []any{&actual.b}, allTypes{b: expected.b}},
807 {"select $1::bool", []any{zero.b}, []any{&actual.b}, allTypes{}},
808 {"select $1::timestamptz", []any{expected.t}, []any{&actual.t}, allTypes{t: expected.t}},
809 {"select $1::timestamptz", []any{zero.t}, []any{&actual.t}, allTypes{}},
810 }
811
812 for i, tt := range tests {
813 actual = zero
814
815 err := conn.QueryRow(context.Background(), tt.sql, tt.queryArgs...).Scan(tt.scanArgs...)
816 if err != nil {
817 t.Errorf("%d. Unexpected failure: %v (sql -> %v, queryArgs -> %v)", i, err, tt.sql, tt.queryArgs)
818 }
819
820 assert.Equal(t, tt.expected.s, actual.s)
821 assert.Equal(t, tt.expected.i16, actual.i16)
822 assert.Equal(t, tt.expected.i32, actual.i32)
823 assert.Equal(t, tt.expected.i64, actual.i64)
824 assert.Equal(t, tt.expected.f32, actual.f32)
825 assert.Equal(t, tt.expected.f64, actual.f64)
826 assert.Equal(t, tt.expected.b, actual.b)
827 if tt.expected.t != nil || actual.t != nil {
828 assert.True(t, tt.expected.t.Equal(*actual.t))
829 }
830
831 ensureConnValid(t, conn)
832 }
833 })
834 }
835
836 func TestPointerPointerNonZero(t *testing.T) {
837 t.Parallel()
838
839 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
840 defer cancel()
841
842 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
843 f := "foo"
844 dest := &f
845
846 err := conn.QueryRow(context.Background(), "select $1::text", nil).Scan(&dest)
847 if err != nil {
848 t.Errorf("Unexpected failure scanning: %v", err)
849 }
850 if dest != nil {
851 t.Errorf("Expected dest to be nil, got %#v", dest)
852 }
853 })
854 }
855
856 func TestEncodeTypeRename(t *testing.T) {
857 t.Parallel()
858
859 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
860 defer cancel()
861
862 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
863 type _int int
864 inInt := _int(1)
865 var outInt _int
866
867 type _int8 int8
868 inInt8 := _int8(2)
869 var outInt8 _int8
870
871 type _int16 int16
872 inInt16 := _int16(3)
873 var outInt16 _int16
874
875 type _int32 int32
876 inInt32 := _int32(4)
877 var outInt32 _int32
878
879 type _int64 int64
880 inInt64 := _int64(5)
881 var outInt64 _int64
882
883 type _uint uint
884 inUint := _uint(6)
885 var outUint _uint
886
887 type _uint8 uint8
888 inUint8 := _uint8(7)
889 var outUint8 _uint8
890
891 type _uint16 uint16
892 inUint16 := _uint16(8)
893 var outUint16 _uint16
894
895 type _uint32 uint32
896 inUint32 := _uint32(9)
897 var outUint32 _uint32
898
899 type _uint64 uint64
900 inUint64 := _uint64(10)
901 var outUint64 _uint64
902
903 type _string string
904 inString := _string("foo")
905 var outString _string
906
907 type _bool bool
908 inBool := _bool(true)
909 var outBool _bool
910
911
912 conn.TypeMap().RegisterDefaultPgType(inInt, "int8")
913 conn.TypeMap().RegisterDefaultPgType(inInt8, "int8")
914 conn.TypeMap().RegisterDefaultPgType(inInt16, "int8")
915 conn.TypeMap().RegisterDefaultPgType(inInt32, "int8")
916 conn.TypeMap().RegisterDefaultPgType(inInt64, "int8")
917 conn.TypeMap().RegisterDefaultPgType(inUint, "int8")
918 conn.TypeMap().RegisterDefaultPgType(inUint8, "int8")
919 conn.TypeMap().RegisterDefaultPgType(inUint16, "int8")
920 conn.TypeMap().RegisterDefaultPgType(inUint32, "int8")
921 conn.TypeMap().RegisterDefaultPgType(inUint64, "int8")
922 conn.TypeMap().RegisterDefaultPgType(inString, "text")
923 conn.TypeMap().RegisterDefaultPgType(inBool, "bool")
924
925 err := conn.QueryRow(context.Background(), "select $1::int, $2::int, $3::int2, $4::int4, $5::int8, $6::int, $7::int, $8::int, $9::int, $10::int, $11::text, $12::bool",
926 inInt, inInt8, inInt16, inInt32, inInt64, inUint, inUint8, inUint16, inUint32, inUint64, inString, inBool,
927 ).Scan(&outInt, &outInt8, &outInt16, &outInt32, &outInt64, &outUint, &outUint8, &outUint16, &outUint32, &outUint64, &outString, &outBool)
928 if err != nil {
929 t.Fatalf("Failed with type rename: %v", err)
930 }
931
932 if inInt != outInt {
933 t.Errorf("int rename: expected %v, got %v", inInt, outInt)
934 }
935
936 if inInt8 != outInt8 {
937 t.Errorf("int8 rename: expected %v, got %v", inInt8, outInt8)
938 }
939
940 if inInt16 != outInt16 {
941 t.Errorf("int16 rename: expected %v, got %v", inInt16, outInt16)
942 }
943
944 if inInt32 != outInt32 {
945 t.Errorf("int32 rename: expected %v, got %v", inInt32, outInt32)
946 }
947
948 if inInt64 != outInt64 {
949 t.Errorf("int64 rename: expected %v, got %v", inInt64, outInt64)
950 }
951
952 if inUint != outUint {
953 t.Errorf("uint rename: expected %v, got %v", inUint, outUint)
954 }
955
956 if inUint8 != outUint8 {
957 t.Errorf("uint8 rename: expected %v, got %v", inUint8, outUint8)
958 }
959
960 if inUint16 != outUint16 {
961 t.Errorf("uint16 rename: expected %v, got %v", inUint16, outUint16)
962 }
963
964 if inUint32 != outUint32 {
965 t.Errorf("uint32 rename: expected %v, got %v", inUint32, outUint32)
966 }
967
968 if inUint64 != outUint64 {
969 t.Errorf("uint64 rename: expected %v, got %v", inUint64, outUint64)
970 }
971
972 if inString != outString {
973 t.Errorf("string rename: expected %v, got %v", inString, outString)
974 }
975
976 if inBool != outBool {
977 t.Errorf("bool rename: expected %v, got %v", inBool, outBool)
978 }
979 })
980 }
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028 func TestRowsScanNilThenScanValue(t *testing.T) {
1029 t.Parallel()
1030
1031 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
1032 defer cancel()
1033
1034 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
1035 sql := `select null as a, null as b
1036 union
1037 select 1, 2
1038 order by a nulls first
1039 `
1040 rows, err := conn.Query(context.Background(), sql)
1041 require.NoError(t, err)
1042
1043 require.True(t, rows.Next())
1044
1045 err = rows.Scan(nil, nil)
1046 require.NoError(t, err)
1047
1048 require.True(t, rows.Next())
1049
1050 var a int
1051 var b int
1052 err = rows.Scan(&a, &b)
1053 require.NoError(t, err)
1054
1055 require.EqualValues(t, 1, a)
1056 require.EqualValues(t, 2, b)
1057
1058 rows.Close()
1059 require.NoError(t, rows.Err())
1060 })
1061 }
1062
1063 func TestScanIntoByteSlice(t *testing.T) {
1064 t.Parallel()
1065
1066 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1067 defer closeConn(t, conn)
1068
1069 for _, tt := range []struct {
1070 name string
1071 sql string
1072 resultFormatCode int16
1073 output []byte
1074 }{
1075 {"int - text", "select 42", pgx.TextFormatCode, []byte("42")},
1076 {"int - binary", "select 42", pgx.BinaryFormatCode, []byte("42")},
1077 {"text - text", "select 'hi'", pgx.TextFormatCode, []byte("hi")},
1078 {"text - binary", "select 'hi'", pgx.BinaryFormatCode, []byte("hi")},
1079 {"json - text", "select '{}'::json", pgx.TextFormatCode, []byte("{}")},
1080 {"json - binary", "select '{}'::json", pgx.BinaryFormatCode, []byte("{}")},
1081 {"jsonb - text", "select '{}'::jsonb", pgx.TextFormatCode, []byte("{}")},
1082 {"jsonb - binary", "select '{}'::jsonb", pgx.BinaryFormatCode, []byte("{}")},
1083 } {
1084 t.Run(tt.name, func(t *testing.T) {
1085 var buf []byte
1086 err := conn.QueryRow(context.Background(), tt.sql, pgx.QueryResultFormats{tt.resultFormatCode}).Scan(&buf)
1087 require.NoError(t, err)
1088 require.Equal(t, tt.output, buf)
1089 })
1090 }
1091 }
1092
View as plain text