1 package pgx_test
2
3 import (
4 "bytes"
5 "context"
6 "database/sql"
7 "errors"
8 "fmt"
9 "os"
10 "strconv"
11 "strings"
12 "testing"
13 "time"
14
15 "github.com/jackc/pgx/v5"
16 "github.com/jackc/pgx/v5/pgconn"
17 "github.com/jackc/pgx/v5/pgtype"
18 "github.com/jackc/pgx/v5/pgxtest"
19 "github.com/stretchr/testify/assert"
20 "github.com/stretchr/testify/require"
21 )
22
23 func TestConnQueryScan(t *testing.T) {
24 t.Parallel()
25
26 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
27 defer closeConn(t, conn)
28
29 var sum, rowCount int32
30
31 rows, err := conn.Query(context.Background(), "select generate_series(1,$1)", 10)
32 if err != nil {
33 t.Fatalf("conn.Query failed: %v", err)
34 }
35 defer rows.Close()
36
37 for rows.Next() {
38 var n int32
39 rows.Scan(&n)
40 sum += n
41 rowCount++
42 }
43
44 if rows.Err() != nil {
45 t.Fatalf("conn.Query failed: %v", rows.Err())
46 }
47
48 assert.Equal(t, "SELECT 10", rows.CommandTag().String())
49
50 if rowCount != 10 {
51 t.Error("Select called onDataRow wrong number of times")
52 }
53 if sum != 55 {
54 t.Error("Wrong values returned")
55 }
56 }
57
58 func TestConnQueryRowsFieldDescriptionsBeforeNext(t *testing.T) {
59 t.Parallel()
60
61 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
62 defer closeConn(t, conn)
63
64 rows, err := conn.Query(context.Background(), "select 'hello' as msg")
65 require.NoError(t, err)
66 defer rows.Close()
67
68 require.Len(t, rows.FieldDescriptions(), 1)
69 assert.Equal(t, "msg", rows.FieldDescriptions()[0].Name)
70 }
71
72 func TestConnQueryWithoutResultSetCommandTag(t *testing.T) {
73 t.Parallel()
74
75 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
76 defer closeConn(t, conn)
77
78 rows, err := conn.Query(context.Background(), "create temporary table t (id serial);")
79 assert.NoError(t, err)
80 rows.Close()
81 assert.NoError(t, rows.Err())
82 assert.Equal(t, "CREATE TABLE", rows.CommandTag().String())
83 }
84
85 func TestConnQueryScanWithManyColumns(t *testing.T) {
86 t.Parallel()
87
88 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
89 defer closeConn(t, conn)
90
91 columnCount := 1000
92 sql := "select "
93 for i := 0; i < columnCount; i++ {
94 if i > 0 {
95 sql += ","
96 }
97 sql += fmt.Sprintf(" %d", i)
98 }
99 sql += " from generate_series(1,5)"
100
101 dest := make([]int, columnCount)
102
103 var rowCount int
104
105 rows, err := conn.Query(context.Background(), sql)
106 if err != nil {
107 t.Fatalf("conn.Query failed: %v", err)
108 }
109 defer rows.Close()
110
111 for rows.Next() {
112 destPtrs := make([]any, columnCount)
113 for i := range destPtrs {
114 destPtrs[i] = &dest[i]
115 }
116 if err := rows.Scan(destPtrs...); err != nil {
117 t.Fatalf("rows.Scan failed: %v", err)
118 }
119 rowCount++
120
121 for i := range dest {
122 if dest[i] != i {
123 t.Errorf("dest[%d] => %d, want %d", i, dest[i], i)
124 }
125 }
126 }
127
128 if rows.Err() != nil {
129 t.Fatalf("conn.Query failed: %v", rows.Err())
130 }
131
132 if rowCount != 5 {
133 t.Errorf("rowCount => %d, want %d", rowCount, 5)
134 }
135 }
136
137 func TestConnQueryValues(t *testing.T) {
138 t.Parallel()
139
140 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
141 defer closeConn(t, conn)
142
143 var rowCount int32
144
145 rows, err := conn.Query(context.Background(), "select 'foo'::text, 'bar'::varchar, n, null, n from generate_series(1,$1) n", 10)
146 if err != nil {
147 t.Fatalf("conn.Query failed: %v", err)
148 }
149 defer rows.Close()
150
151 for rows.Next() {
152 rowCount++
153
154 values, err := rows.Values()
155 require.NoError(t, err)
156 require.Len(t, values, 5)
157 assert.Equal(t, "foo", values[0])
158 assert.Equal(t, "bar", values[1])
159 assert.EqualValues(t, rowCount, values[2])
160 assert.Nil(t, values[3])
161 assert.EqualValues(t, rowCount, values[4])
162 }
163
164 if rows.Err() != nil {
165 t.Fatalf("conn.Query failed: %v", rows.Err())
166 }
167
168 if rowCount != 10 {
169 t.Error("Select called onDataRow wrong number of times")
170 }
171 }
172
173
174 func TestConnQueryValuesWhenUnableToDecode(t *testing.T) {
175 t.Parallel()
176
177 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
178 defer closeConn(t, conn)
179
180
181
182 rows, err := conn.Query(context.Background(), "select (array[1::oid], null)", pgx.QueryResultFormats{pgx.TextFormatCode})
183 require.NoError(t, err)
184 defer rows.Close()
185
186 require.True(t, rows.Next())
187
188 values, err := rows.Values()
189 require.NoError(t, err)
190 require.Equal(t, "({1},)", values[0])
191 }
192
193 func TestConnQueryValuesWithUnregisteredOID(t *testing.T) {
194 t.Parallel()
195
196 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
197 defer cancel()
198
199 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
200 defer closeConn(t, conn)
201
202 tx, err := conn.Begin(ctx)
203 require.NoError(t, err)
204 defer tx.Rollback(ctx)
205
206 _, err = tx.Exec(ctx, "create type fruit as enum('orange', 'apple', 'pear')")
207 require.NoError(t, err)
208
209 rows, err := conn.Query(context.Background(), "select 'orange'::fruit")
210 require.NoError(t, err)
211 defer rows.Close()
212
213 require.True(t, rows.Next())
214
215 values, err := rows.Values()
216 require.NoError(t, err)
217 require.Equal(t, "orange", values[0])
218 }
219
220 func TestConnQueryArgsAndScanWithUnregisteredOID(t *testing.T) {
221 t.Parallel()
222
223 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
224 defer cancel()
225
226 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
227 tx, err := conn.Begin(ctx)
228 require.NoError(t, err)
229 defer tx.Rollback(ctx)
230
231 _, err = tx.Exec(ctx, "create type fruit as enum('orange', 'apple', 'pear')")
232 require.NoError(t, err)
233
234 var result string
235 err = conn.QueryRow(ctx, "select $1::fruit", "orange").Scan(&result)
236 require.NoError(t, err)
237 require.Equal(t, "orange", result)
238 })
239 }
240
241
242 func TestConnQueryReadRowMultipleTimes(t *testing.T) {
243 t.Parallel()
244
245 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
246 defer closeConn(t, conn)
247
248 var rowCount int32
249
250 rows, err := conn.Query(context.Background(), "select 'foo'::text, 'bar'::varchar, n, null, n from generate_series(1,$1) n", 10)
251 require.NoError(t, err)
252 defer rows.Close()
253
254 for rows.Next() {
255 rowCount++
256
257 for i := 0; i < 2; i++ {
258 values, err := rows.Values()
259 require.NoError(t, err)
260 require.Len(t, values, 5)
261 require.Equal(t, "foo", values[0])
262 require.Equal(t, "bar", values[1])
263 require.EqualValues(t, rowCount, values[2])
264 require.Nil(t, values[3])
265 require.EqualValues(t, rowCount, values[4])
266
267 var a, b string
268 var c int32
269 var d pgtype.Text
270 var e int32
271
272 err = rows.Scan(&a, &b, &c, &d, &e)
273 require.NoError(t, err)
274 require.Equal(t, "foo", a)
275 require.Equal(t, "bar", b)
276 require.Equal(t, rowCount, c)
277 require.False(t, d.Valid)
278 require.Equal(t, rowCount, e)
279 }
280 }
281
282 require.NoError(t, rows.Err())
283 require.Equal(t, int32(10), rowCount)
284 }
285
286
287 func TestRowsScanDoesNotAllowScanningBinaryFormatValuesIntoString(t *testing.T) {
288 t.Parallel()
289
290 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
291 defer closeConn(t, conn)
292
293 pgxtest.SkipCockroachDB(t, conn, "Server does not support point type")
294
295 var s string
296
297 err := conn.QueryRow(context.Background(), "select point(1,2)").Scan(&s)
298 if err == nil || !(strings.Contains(err.Error(), "cannot scan point (OID 600) in binary format into *string")) {
299 t.Fatalf("Expected Scan to fail to scan binary value into string but: %v", err)
300 }
301
302 ensureConnValid(t, conn)
303 }
304
305 func TestConnQueryRawValues(t *testing.T) {
306 t.Parallel()
307
308 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
309 defer closeConn(t, conn)
310
311 var rowCount int32
312
313 rows, err := conn.Query(
314 context.Background(),
315 "select 'foo'::text, 'bar'::varchar, n, null, n from generate_series(1,$1) n",
316 pgx.QueryExecModeSimpleProtocol,
317 10,
318 )
319 require.NoError(t, err)
320 defer rows.Close()
321
322 for rows.Next() {
323 rowCount++
324
325 rawValues := rows.RawValues()
326 assert.Len(t, rawValues, 5)
327 assert.Equal(t, "foo", string(rawValues[0]))
328 assert.Equal(t, "bar", string(rawValues[1]))
329 assert.Equal(t, strconv.FormatInt(int64(rowCount), 10), string(rawValues[2]))
330 assert.Nil(t, rawValues[3])
331 assert.Equal(t, strconv.FormatInt(int64(rowCount), 10), string(rawValues[4]))
332 }
333
334 require.NoError(t, rows.Err())
335 assert.EqualValues(t, 10, rowCount)
336 }
337
338
339 func TestConnQueryCloseEarly(t *testing.T) {
340 t.Parallel()
341
342 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
343 defer closeConn(t, conn)
344
345
346 rows, err := conn.Query(context.Background(), "select generate_series(1,$1)", 10)
347 if err != nil {
348 t.Fatalf("conn.Query failed: %v", err)
349 }
350 rows.Close()
351
352 ensureConnValid(t, conn)
353
354
355 rows, err = conn.Query(context.Background(), "select generate_series(1,$1)", 10)
356 if err != nil {
357 t.Fatalf("conn.Query failed: %v", err)
358 }
359
360 ok := rows.Next()
361 if !ok {
362 t.Fatal("rows.Next terminated early")
363 }
364
365 var n int32
366 rows.Scan(&n)
367 if n != 1 {
368 t.Fatalf("Expected 1 from first row, but got %v", n)
369 }
370
371 rows.Close()
372
373 ensureConnValid(t, conn)
374 }
375
376 func TestConnQueryCloseEarlyWithErrorOnWire(t *testing.T) {
377 t.Parallel()
378
379 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
380 defer closeConn(t, conn)
381
382 rows, err := conn.Query(context.Background(), "select 1/(10-n) from generate_series(1,10) n")
383 if err != nil {
384 t.Fatalf("conn.Query failed: %v", err)
385 }
386 assert.False(t, pgconn.SafeToRetry(err))
387 rows.Close()
388
389 ensureConnValid(t, conn)
390 }
391
392
393 func TestConnQueryReadWrongTypeError(t *testing.T) {
394 t.Parallel()
395
396 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
397 defer closeConn(t, conn)
398
399
400 rows, err := conn.Query(context.Background(), "select n::int4 from generate_series(1,$1) n", 10)
401 if err != nil {
402 t.Fatalf("conn.Query failed: %v", err)
403 }
404
405 rowsRead := 0
406
407 for rows.Next() {
408 var t time.Time
409 rows.Scan(&t)
410 rowsRead++
411 }
412
413 if rowsRead != 1 {
414 t.Fatalf("Expected error to cause only 1 row to be read, but %d were read", rowsRead)
415 }
416
417 if rows.Err() == nil {
418 t.Fatal("Expected Rows to have an error after an improper read but it didn't")
419 }
420
421 if rows.Err().Error() != "can't scan into dest[0]: cannot scan int4 (OID 23) in binary format into *time.Time" {
422 t.Fatalf("Expected different Rows.Err(): %v", rows.Err())
423 }
424
425 ensureConnValid(t, conn)
426 }
427
428
429 func TestConnQueryReadTooManyValues(t *testing.T) {
430 t.Parallel()
431
432 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
433 defer closeConn(t, conn)
434
435
436 rows, err := conn.Query(context.Background(), "select generate_series(1,$1)", 10)
437 if err != nil {
438 t.Fatalf("conn.Query failed: %v", err)
439 }
440
441 rowsRead := 0
442
443 for rows.Next() {
444 var n, m int32
445 rows.Scan(&n, &m)
446 rowsRead++
447 }
448
449 if rowsRead != 1 {
450 t.Fatalf("Expected error to cause only 1 row to be read, but %d were read", rowsRead)
451 }
452
453 if rows.Err() == nil {
454 t.Fatal("Expected Rows to have an error after an improper read but it didn't")
455 }
456
457 ensureConnValid(t, conn)
458 }
459
460 func TestConnQueryScanIgnoreColumn(t *testing.T) {
461 t.Parallel()
462
463 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
464 defer closeConn(t, conn)
465
466 rows, err := conn.Query(context.Background(), "select 1::int8, 2::int8, 3::int8")
467 if err != nil {
468 t.Fatalf("conn.Query failed: %v", err)
469 }
470
471 ok := rows.Next()
472 if !ok {
473 t.Fatal("rows.Next terminated early")
474 }
475
476 var n, m int64
477 err = rows.Scan(&n, nil, &m)
478 if err != nil {
479 t.Fatalf("rows.Scan failed: %v", err)
480 }
481 rows.Close()
482
483 if n != 1 {
484 t.Errorf("Expected n to equal 1, but it was %d", n)
485 }
486
487 if m != 3 {
488 t.Errorf("Expected n to equal 3, but it was %d", m)
489 }
490
491 ensureConnValid(t, conn)
492 }
493
494
495 func TestConnQueryDeferredError(t *testing.T) {
496 t.Parallel()
497
498 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
499 defer closeConn(t, conn)
500
501 pgxtest.SkipCockroachDB(t, conn, "Server does not support deferred constraint (https://github.com/cockroachdb/cockroach/issues/31632)")
502
503 mustExec(t, conn, `create temporary table t (
504 id text primary key,
505 n int not null,
506 unique (n) deferrable initially deferred
507 );
508
509 insert into t (id, n) values ('a', 1), ('b', 2), ('c', 3);`)
510
511 rows, err := conn.Query(context.Background(), `update t set n=n+1 where id='b' returning *`)
512 if err != nil {
513 t.Fatal(err)
514 }
515 defer rows.Close()
516
517 for rows.Next() {
518 var id string
519 var n int32
520 err = rows.Scan(&id, &n)
521 if err != nil {
522 t.Fatal(err)
523 }
524 }
525
526 if rows.Err() == nil {
527 t.Fatal("expected error 23505 but got none")
528 }
529
530 if err, ok := rows.Err().(*pgconn.PgError); !ok || err.Code != "23505" {
531 t.Fatalf("expected error 23505, got %v", err)
532 }
533
534 ensureConnValid(t, conn)
535 }
536
537 func TestConnQueryErrorWhileReturningRows(t *testing.T) {
538 t.Parallel()
539
540 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
541 defer closeConn(t, conn)
542
543 pgxtest.SkipCockroachDB(t, conn, "Server uses numeric instead of int")
544
545 for i := 0; i < 100; i++ {
546 func() {
547 sql := `select 42 / (random() * 20)::integer from generate_series(1,100000)`
548
549 rows, err := conn.Query(context.Background(), sql)
550 if err != nil {
551 t.Fatal(err)
552 }
553 defer rows.Close()
554
555 for rows.Next() {
556 var n int32
557 if err := rows.Scan(&n); err != nil {
558 t.Fatalf("Row scan failed: %v", err)
559 }
560 }
561
562 if _, ok := rows.Err().(*pgconn.PgError); !ok {
563 t.Fatalf("Expected pgx.PgError, got %v", rows.Err())
564 }
565
566 ensureConnValid(t, conn)
567 }()
568 }
569
570 }
571
572 func TestQueryEncodeError(t *testing.T) {
573 t.Parallel()
574
575 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
576 defer closeConn(t, conn)
577
578 rows, err := conn.Query(context.Background(), "select $1::integer", "wrong")
579 if err != nil {
580 t.Errorf("conn.Query failure: %v", err)
581 }
582 assert.False(t, pgconn.SafeToRetry(err))
583 defer rows.Close()
584
585 rows.Next()
586
587 if rows.Err() == nil {
588 t.Error("Expected rows.Err() to return error, but it didn't")
589 }
590 if !strings.Contains(rows.Err().Error(), "SQLSTATE 22P02") {
591 t.Error("Expected rows.Err() to return different error:", rows.Err())
592 }
593 }
594
595 func TestQueryRowCoreTypes(t *testing.T) {
596 t.Parallel()
597
598 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
599 defer closeConn(t, conn)
600
601 type allTypes struct {
602 s string
603 f32 float32
604 f64 float64
605 b bool
606 t time.Time
607 oid uint32
608 }
609
610 var actual, zero allTypes
611
612 tests := []struct {
613 sql string
614 queryArgs []any
615 scanArgs []any
616 expected allTypes
617 }{
618 {"select $1::text", []any{"Jack"}, []any{&actual.s}, allTypes{s: "Jack"}},
619 {"select $1::float4", []any{float32(1.23)}, []any{&actual.f32}, allTypes{f32: 1.23}},
620 {"select $1::float8", []any{float64(1.23)}, []any{&actual.f64}, allTypes{f64: 1.23}},
621 {"select $1::bool", []any{true}, []any{&actual.b}, allTypes{b: true}},
622 {"select $1::timestamptz", []any{time.Unix(123, 5000)}, []any{&actual.t}, allTypes{t: time.Unix(123, 5000)}},
623 {"select $1::timestamp", []any{time.Date(2010, 1, 2, 3, 4, 5, 0, time.UTC)}, []any{&actual.t}, allTypes{t: time.Date(2010, 1, 2, 3, 4, 5, 0, time.UTC)}},
624 {"select $1::date", []any{time.Date(1987, 1, 2, 0, 0, 0, 0, time.UTC)}, []any{&actual.t}, allTypes{t: time.Date(1987, 1, 2, 0, 0, 0, 0, time.UTC)}},
625 {"select $1::oid", []any{uint32(42)}, []any{&actual.oid}, allTypes{oid: 42}},
626 }
627
628 for i, tt := range tests {
629 actual = zero
630
631 err := conn.QueryRow(context.Background(), tt.sql, tt.queryArgs...).Scan(tt.scanArgs...)
632 if err != nil {
633 t.Errorf("%d. Unexpected failure: %v (sql -> %v, queryArgs -> %v)", i, err, tt.sql, tt.queryArgs)
634 }
635
636 if actual.s != tt.expected.s || actual.f32 != tt.expected.f32 || actual.b != tt.expected.b || !actual.t.Equal(tt.expected.t) || actual.oid != tt.expected.oid {
637 t.Errorf("%d. Expected %v, got %v (sql -> %v, queryArgs -> %v)", i, tt.expected, actual, tt.sql, tt.queryArgs)
638 }
639
640 ensureConnValid(t, conn)
641
642
643 err = conn.QueryRow(context.Background(), tt.sql, nil).Scan(tt.scanArgs...)
644 if err == nil {
645 t.Errorf("%d. Expected null to cause error, but it didn't (sql -> %v)", i, tt.sql)
646 }
647
648 ensureConnValid(t, conn)
649 }
650 }
651
652 func TestQueryRowCoreIntegerEncoding(t *testing.T) {
653 t.Parallel()
654
655 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
656 defer closeConn(t, conn)
657
658 type allTypes struct {
659 i16 int16
660 i32 int32
661 i64 int64
662 }
663
664 var actual, zero allTypes
665
666 successfulEncodeTests := []struct {
667 sql string
668 queryArg any
669 scanArg any
670 expected allTypes
671 }{
672
673 {"select $1::int2", int(42), &actual.i16, allTypes{i16: 42}},
674 {"select $1::int2", int8(42), &actual.i16, allTypes{i16: 42}},
675 {"select $1::int2", int16(42), &actual.i16, allTypes{i16: 42}},
676 {"select $1::int2", int32(42), &actual.i16, allTypes{i16: 42}},
677 {"select $1::int2", int64(42), &actual.i16, allTypes{i16: 42}},
678 {"select $1::int2", uint(42), &actual.i16, allTypes{i16: 42}},
679 {"select $1::int2", uint8(42), &actual.i16, allTypes{i16: 42}},
680 {"select $1::int2", uint16(42), &actual.i16, allTypes{i16: 42}},
681 {"select $1::int2", uint32(42), &actual.i16, allTypes{i16: 42}},
682 {"select $1::int2", uint64(42), &actual.i16, allTypes{i16: 42}},
683
684
685 {"select $1::int4", int(42), &actual.i32, allTypes{i32: 42}},
686 {"select $1::int4", int8(42), &actual.i32, allTypes{i32: 42}},
687 {"select $1::int4", int16(42), &actual.i32, allTypes{i32: 42}},
688 {"select $1::int4", int32(42), &actual.i32, allTypes{i32: 42}},
689 {"select $1::int4", int64(42), &actual.i32, allTypes{i32: 42}},
690 {"select $1::int4", uint(42), &actual.i32, allTypes{i32: 42}},
691 {"select $1::int4", uint8(42), &actual.i32, allTypes{i32: 42}},
692 {"select $1::int4", uint16(42), &actual.i32, allTypes{i32: 42}},
693 {"select $1::int4", uint32(42), &actual.i32, allTypes{i32: 42}},
694 {"select $1::int4", uint64(42), &actual.i32, allTypes{i32: 42}},
695
696
697 {"select $1::int8", int(42), &actual.i64, allTypes{i64: 42}},
698 {"select $1::int8", int8(42), &actual.i64, allTypes{i64: 42}},
699 {"select $1::int8", int16(42), &actual.i64, allTypes{i64: 42}},
700 {"select $1::int8", int32(42), &actual.i64, allTypes{i64: 42}},
701 {"select $1::int8", int64(42), &actual.i64, allTypes{i64: 42}},
702 {"select $1::int8", uint(42), &actual.i64, allTypes{i64: 42}},
703 {"select $1::int8", uint8(42), &actual.i64, allTypes{i64: 42}},
704 {"select $1::int8", uint16(42), &actual.i64, allTypes{i64: 42}},
705 {"select $1::int8", uint32(42), &actual.i64, allTypes{i64: 42}},
706 {"select $1::int8", uint64(42), &actual.i64, allTypes{i64: 42}},
707 }
708
709 for i, tt := range successfulEncodeTests {
710 actual = zero
711
712 err := conn.QueryRow(context.Background(), tt.sql, tt.queryArg).Scan(tt.scanArg)
713 if err != nil {
714 t.Errorf("%d. Unexpected failure: %v (sql -> %v, queryArg -> %v)", i, err, tt.sql, tt.queryArg)
715 continue
716 }
717
718 if actual != tt.expected {
719 t.Errorf("%d. Expected %v, got %v (sql -> %v, queryArg -> %v)", i, tt.expected, actual, tt.sql, tt.queryArg)
720 }
721
722 ensureConnValid(t, conn)
723 }
724
725 failedEncodeTests := []struct {
726 sql string
727 queryArg any
728 }{
729
730 {"select $1::int2", int(32769)},
731 {"select $1::int2", int32(32769)},
732 {"select $1::int2", int32(32769)},
733 {"select $1::int2", int64(32769)},
734 {"select $1::int2", uint(32769)},
735 {"select $1::int2", uint16(32769)},
736 {"select $1::int2", uint32(32769)},
737 {"select $1::int2", uint64(32769)},
738
739
740 {"select $1::int4", int64(2147483649)},
741 {"select $1::int4", uint32(2147483649)},
742 {"select $1::int4", uint64(2147483649)},
743
744
745 {"select $1::int8", uint64(9223372036854775809)},
746 }
747
748 for i, tt := range failedEncodeTests {
749 err := conn.QueryRow(context.Background(), tt.sql, tt.queryArg).Scan(nil)
750 if err == nil {
751 t.Errorf("%d. Expected failure to encode, but unexpectedly succeeded: %v (sql -> %v, queryArg -> %v)", i, err, tt.sql, tt.queryArg)
752 } else if !strings.Contains(err.Error(), "is greater than") {
753 t.Errorf("%d. Expected failure to encode, but got: %v (sql -> %v, queryArg -> %v)", i, err, tt.sql, tt.queryArg)
754 }
755
756 ensureConnValid(t, conn)
757 }
758 }
759
760 func TestQueryRowCoreIntegerDecoding(t *testing.T) {
761 t.Parallel()
762
763 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
764 defer closeConn(t, conn)
765
766 type allTypes struct {
767 ui uint
768 ui8 uint8
769 ui16 uint16
770 ui32 uint32
771 ui64 uint64
772 i int
773 i8 int8
774 i16 int16
775 i32 int32
776 i64 int64
777 }
778
779 var actual, zero allTypes
780
781 successfulDecodeTests := []struct {
782 sql string
783 scanArg any
784 expected allTypes
785 }{
786
787 {"select 42::int2", &actual.i, allTypes{i: 42}},
788 {"select 42::int4", &actual.i, allTypes{i: 42}},
789 {"select 42::int8", &actual.i, allTypes{i: 42}},
790 {"select -42::int2", &actual.i, allTypes{i: -42}},
791 {"select -42::int4", &actual.i, allTypes{i: -42}},
792 {"select -42::int8", &actual.i, allTypes{i: -42}},
793
794
795 {"select 42::int2", &actual.i8, allTypes{i8: 42}},
796 {"select 42::int4", &actual.i8, allTypes{i8: 42}},
797 {"select 42::int8", &actual.i8, allTypes{i8: 42}},
798 {"select -42::int2", &actual.i8, allTypes{i8: -42}},
799 {"select -42::int4", &actual.i8, allTypes{i8: -42}},
800 {"select -42::int8", &actual.i8, allTypes{i8: -42}},
801
802
803 {"select 42::int2", &actual.i16, allTypes{i16: 42}},
804 {"select 42::int4", &actual.i16, allTypes{i16: 42}},
805 {"select 42::int8", &actual.i16, allTypes{i16: 42}},
806 {"select -42::int2", &actual.i16, allTypes{i16: -42}},
807 {"select -42::int4", &actual.i16, allTypes{i16: -42}},
808 {"select -42::int8", &actual.i16, allTypes{i16: -42}},
809
810
811 {"select 42::int2", &actual.i32, allTypes{i32: 42}},
812 {"select 42::int4", &actual.i32, allTypes{i32: 42}},
813 {"select 42::int8", &actual.i32, allTypes{i32: 42}},
814 {"select -42::int2", &actual.i32, allTypes{i32: -42}},
815 {"select -42::int4", &actual.i32, allTypes{i32: -42}},
816 {"select -42::int8", &actual.i32, allTypes{i32: -42}},
817
818
819 {"select 42::int2", &actual.i64, allTypes{i64: 42}},
820 {"select 42::int4", &actual.i64, allTypes{i64: 42}},
821 {"select 42::int8", &actual.i64, allTypes{i64: 42}},
822 {"select -42::int2", &actual.i64, allTypes{i64: -42}},
823 {"select -42::int4", &actual.i64, allTypes{i64: -42}},
824 {"select -42::int8", &actual.i64, allTypes{i64: -42}},
825
826
827 {"select 128::int2", &actual.ui, allTypes{ui: 128}},
828 {"select 128::int4", &actual.ui, allTypes{ui: 128}},
829 {"select 128::int8", &actual.ui, allTypes{ui: 128}},
830
831
832 {"select 128::int2", &actual.ui8, allTypes{ui8: 128}},
833 {"select 128::int4", &actual.ui8, allTypes{ui8: 128}},
834 {"select 128::int8", &actual.ui8, allTypes{ui8: 128}},
835
836
837 {"select 42::int2", &actual.ui16, allTypes{ui16: 42}},
838 {"select 32768::int4", &actual.ui16, allTypes{ui16: 32768}},
839 {"select 32768::int8", &actual.ui16, allTypes{ui16: 32768}},
840
841
842 {"select 42::int2", &actual.ui32, allTypes{ui32: 42}},
843 {"select 42::int4", &actual.ui32, allTypes{ui32: 42}},
844 {"select 2147483648::int8", &actual.ui32, allTypes{ui32: 2147483648}},
845
846
847 {"select 42::int2", &actual.ui64, allTypes{ui64: 42}},
848 {"select 42::int4", &actual.ui64, allTypes{ui64: 42}},
849 {"select 42::int8", &actual.ui64, allTypes{ui64: 42}},
850 }
851
852 for i, tt := range successfulDecodeTests {
853 actual = zero
854
855 err := conn.QueryRow(context.Background(), tt.sql).Scan(tt.scanArg)
856 if err != nil {
857 t.Errorf("%d. Unexpected failure: %v (sql -> %v)", i, err, tt.sql)
858 continue
859 }
860
861 if actual != tt.expected {
862 t.Errorf("%d. Expected %v, got %v (sql -> %v)", i, tt.expected, actual, tt.sql)
863 }
864
865 ensureConnValid(t, conn)
866 }
867
868 failedDecodeTests := []struct {
869 sql string
870 scanArg any
871 }{
872
873 {"select 128::int2", &actual.i8},
874 {"select 128::int4", &actual.i8},
875 {"select 128::int8", &actual.i8},
876 {"select -129::int2", &actual.i8},
877 {"select -129::int4", &actual.i8},
878 {"select -129::int8", &actual.i8},
879
880
881 {"select 32768::int4", &actual.i16},
882 {"select 32768::int8", &actual.i16},
883 {"select -32769::int4", &actual.i16},
884 {"select -32769::int8", &actual.i16},
885
886
887 {"select 2147483648::int8", &actual.i32},
888 {"select -2147483649::int8", &actual.i32},
889
890
891 {"select -1::int2", &actual.ui},
892 {"select -1::int4", &actual.ui},
893 {"select -1::int8", &actual.ui},
894
895
896 {"select 256::int2", &actual.ui8},
897 {"select 256::int4", &actual.ui8},
898 {"select 256::int8", &actual.ui8},
899 {"select -1::int2", &actual.ui8},
900 {"select -1::int4", &actual.ui8},
901 {"select -1::int8", &actual.ui8},
902
903
904 {"select 65536::int4", &actual.ui16},
905 {"select 65536::int8", &actual.ui16},
906 {"select -1::int2", &actual.ui16},
907 {"select -1::int4", &actual.ui16},
908 {"select -1::int8", &actual.ui16},
909
910
911 {"select 4294967296::int8", &actual.ui32},
912 {"select -1::int2", &actual.ui32},
913 {"select -1::int4", &actual.ui32},
914 {"select -1::int8", &actual.ui32},
915
916
917 {"select -1::int2", &actual.ui64},
918 {"select -1::int4", &actual.ui64},
919 {"select -1::int8", &actual.ui64},
920 }
921
922 for i, tt := range failedDecodeTests {
923 err := conn.QueryRow(context.Background(), tt.sql).Scan(tt.scanArg)
924 if err == nil {
925 t.Errorf("%d. Expected failure to decode, but unexpectedly succeeded: %v (sql -> %v)", i, err, tt.sql)
926 } else if !strings.Contains(err.Error(), "can't scan") {
927 t.Errorf("%d. Expected failure to decode, but got: %v (sql -> %v)", i, err, tt.sql)
928 }
929
930 ensureConnValid(t, conn)
931 }
932 }
933
934 func TestQueryRowCoreByteSlice(t *testing.T) {
935 t.Parallel()
936
937 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
938 defer closeConn(t, conn)
939
940 tests := []struct {
941 sql string
942 queryArg any
943 expected []byte
944 }{
945 {"select $1::text", "Jack", []byte("Jack")},
946 {"select $1::text", []byte("Jack"), []byte("Jack")},
947 {"select $1::varchar", []byte("Jack"), []byte("Jack")},
948 {"select $1::bytea", []byte{0, 15, 255, 17}, []byte{0, 15, 255, 17}},
949 }
950
951 for i, tt := range tests {
952 var actual []byte
953
954 err := conn.QueryRow(context.Background(), tt.sql, tt.queryArg).Scan(&actual)
955 if err != nil {
956 t.Errorf("%d. Unexpected failure: %v (sql -> %v)", i, err, tt.sql)
957 }
958
959 if !bytes.Equal(actual, tt.expected) {
960 t.Errorf("%d. Expected %v, got %v (sql -> %v)", i, tt.expected, actual, tt.sql)
961 }
962
963 ensureConnValid(t, conn)
964 }
965 }
966
967 func TestQueryRowErrors(t *testing.T) {
968 t.Parallel()
969
970 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
971 defer closeConn(t, conn)
972
973 if conn.PgConn().ParameterStatus("crdb_version") != "" {
974 t.Skip("Skipping due to known server missing point type")
975 }
976
977 type allTypes struct {
978 i16 int16
979 s string
980 }
981
982 var actual, zero allTypes
983
984 tests := []struct {
985 sql string
986 queryArgs []any
987 scanArgs []any
988 err string
989 }{
990 {"select $1::badtype", []any{"Jack"}, []any{&actual.i16}, `type "badtype" does not exist`},
991 {"SYNTAX ERROR", []any{}, []any{&actual.i16}, "SQLSTATE 42601"},
992 {"select $1::text", []any{"Jack"}, []any{&actual.i16}, "cannot scan text (OID 25) in text format into *int16"},
993 {"select $1::point", []any{int(705)}, []any{&actual.s}, "unable to encode 705 into binary format for point (OID 600)"},
994 }
995
996 for i, tt := range tests {
997 actual = zero
998
999 err := conn.QueryRow(context.Background(), tt.sql, tt.queryArgs...).Scan(tt.scanArgs...)
1000 if err == nil {
1001 t.Errorf("%d. Unexpected success (sql -> %v, queryArgs -> %v)", i, tt.sql, tt.queryArgs)
1002 }
1003 if err != nil && !strings.Contains(err.Error(), tt.err) {
1004 t.Errorf("%d. Expected error to contain %s, but got %v (sql -> %v, queryArgs -> %v)", i, tt.err, err, tt.sql, tt.queryArgs)
1005 }
1006
1007 ensureConnValid(t, conn)
1008 }
1009 }
1010
1011 func TestQueryRowNoResults(t *testing.T) {
1012 t.Parallel()
1013
1014 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1015 defer closeConn(t, conn)
1016
1017 var n int32
1018 err := conn.QueryRow(context.Background(), "select 1 where 1=0").Scan(&n)
1019 if err != pgx.ErrNoRows {
1020 t.Errorf("Expected pgx.ErrNoRows, got %v", err)
1021 }
1022
1023 ensureConnValid(t, conn)
1024 }
1025
1026 func TestQueryRowEmptyQuery(t *testing.T) {
1027 t.Parallel()
1028
1029 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1030 defer closeConn(t, conn)
1031
1032 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
1033 defer cancel()
1034
1035 var n int32
1036 err := conn.QueryRow(ctx, "").Scan(&n)
1037 require.Error(t, err)
1038 require.False(t, pgconn.Timeout(err))
1039
1040 ensureConnValid(t, conn)
1041 }
1042
1043 func TestReadingValueAfterEmptyArray(t *testing.T) {
1044 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1045 defer closeConn(t, conn)
1046
1047 var a []string
1048 var b int32
1049 err := conn.QueryRow(context.Background(), "select '{}'::text[], 42::integer").Scan(&a, &b)
1050 if err != nil {
1051 t.Fatalf("conn.QueryRow failed: %v", err)
1052 }
1053
1054 if len(a) != 0 {
1055 t.Errorf("Expected 'a' to have length 0, but it was: %d", len(a))
1056 }
1057
1058 if b != 42 {
1059 t.Errorf("Expected 'b' to 42, but it was: %d", b)
1060 }
1061 }
1062
1063 func TestReadingNullByteArray(t *testing.T) {
1064 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1065 defer closeConn(t, conn)
1066
1067 var a []byte
1068 err := conn.QueryRow(context.Background(), "select null::text").Scan(&a)
1069 if err != nil {
1070 t.Fatalf("conn.QueryRow failed: %v", err)
1071 }
1072
1073 if a != nil {
1074 t.Errorf("Expected 'a' to be nil, but it was: %v", a)
1075 }
1076 }
1077
1078 func TestReadingNullByteArrays(t *testing.T) {
1079 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1080 defer closeConn(t, conn)
1081
1082 rows, err := conn.Query(context.Background(), "select null::text union all select null::text")
1083 if err != nil {
1084 t.Fatalf("conn.Query failed: %v", err)
1085 }
1086
1087 count := 0
1088 for rows.Next() {
1089 count++
1090 var a []byte
1091 if err := rows.Scan(&a); err != nil {
1092 t.Fatalf("failed to scan row: %v", err)
1093 }
1094 if a != nil {
1095 t.Errorf("Expected 'a' to be nil, but it was: %v", a)
1096 }
1097 }
1098 if count != 2 {
1099 t.Errorf("Expected to read 2 rows, read: %d", count)
1100 }
1101 }
1102
1103 func TestQueryNullSliceIsSet(t *testing.T) {
1104 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1105 defer closeConn(t, conn)
1106
1107 a := []int32{1, 2, 3}
1108 err := conn.QueryRow(context.Background(), "select null::int[]").Scan(&a)
1109 if err != nil {
1110 t.Fatalf("conn.QueryRow failed: %v", err)
1111 }
1112
1113 if a != nil {
1114 t.Errorf("Expected 'a' to be nil, but it was: %v", a)
1115 }
1116 }
1117
1118 func TestConnQueryDatabaseSQLScanner(t *testing.T) {
1119 t.Parallel()
1120
1121 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1122 defer closeConn(t, conn)
1123
1124 var num sql.NullFloat64
1125
1126 err := conn.QueryRow(context.Background(), "select '1234.567'::float8").Scan(&num)
1127 if err != nil {
1128 t.Fatalf("Scan failed: %v", err)
1129 }
1130
1131 require.True(t, num.Valid)
1132 require.Equal(t, 1234.567, num.Float64)
1133
1134 ensureConnValid(t, conn)
1135 }
1136
1137 func TestConnQueryDatabaseSQLDriverValuer(t *testing.T) {
1138 t.Parallel()
1139
1140 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1141 defer closeConn(t, conn)
1142
1143 expected := sql.NullFloat64{Float64: 1234.567, Valid: true}
1144 var actual sql.NullFloat64
1145
1146 err := conn.QueryRow(context.Background(), "select $1::float8", &expected).Scan(&actual)
1147 require.NoError(t, err)
1148 require.Equal(t, expected, actual)
1149
1150 ensureConnValid(t, conn)
1151 }
1152
1153
1154 func TestConnQueryDatabaseSQLDriverValuerWithAutoGeneratedPointerReceiver(t *testing.T) {
1155 t.Parallel()
1156
1157 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1158 defer closeConn(t, conn)
1159
1160 mustExec(t, conn, "create temporary table t(n numeric)")
1161
1162 var d *sql.NullInt64
1163 commandTag, err := conn.Exec(context.Background(), `insert into t(n) values($1)`, d)
1164 if err != nil {
1165 t.Fatal(err)
1166 }
1167 if commandTag.String() != "INSERT 0 1" {
1168 t.Fatalf("want %s, got %s", "INSERT 0 1", commandTag)
1169 }
1170
1171 ensureConnValid(t, conn)
1172 }
1173
1174 func TestConnQueryDatabaseSQLDriverScannerWithBinaryPgTypeThatAcceptsSameType(t *testing.T) {
1175 t.Parallel()
1176
1177 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1178 defer closeConn(t, conn)
1179
1180 var actual sql.NullString
1181 err := conn.QueryRow(context.Background(), "select '6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid").Scan(&actual)
1182 require.NoError(t, err)
1183
1184 require.True(t, actual.Valid)
1185 require.Equal(t, "6ba7b810-9dad-11d1-80b4-00c04fd430c8", actual.String)
1186
1187 ensureConnValid(t, conn)
1188 }
1189
1190
1191 func TestConnQueryDatabaseSQLDriverValuerTextWhenBinaryIsPreferred(t *testing.T) {
1192 t.Parallel()
1193
1194 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1195 defer closeConn(t, conn)
1196
1197 arg := sql.NullString{String: "1.234", Valid: true}
1198 var result pgtype.Numeric
1199 err := conn.QueryRow(context.Background(), "select $1::numeric", arg).Scan(&result)
1200 require.NoError(t, err)
1201
1202 require.True(t, result.Valid)
1203 f64, err := result.Float64Value()
1204 require.NoError(t, err)
1205 require.Equal(t, pgtype.Float8{Float64: 1.234, Valid: true}, f64)
1206
1207 ensureConnValid(t, conn)
1208 }
1209
1210
1211 func TestConnQueryDatabaseSQLNullFloat64NegativeZeroPointZero(t *testing.T) {
1212 t.Parallel()
1213
1214 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1215 defer closeConn(t, conn)
1216
1217 tests := []float64{
1218 -0.01,
1219 -0.001,
1220 -0.0001,
1221 }
1222
1223 for _, val := range tests {
1224 var result sql.NullFloat64
1225 err := conn.QueryRow(context.Background(), "select $1::numeric", val).Scan(&result)
1226 require.NoError(t, err)
1227 require.Equal(t, sql.NullFloat64{Float64: val, Valid: true}, result)
1228 }
1229
1230 ensureConnValid(t, conn)
1231 }
1232
1233 func TestConnQueryDatabaseSQLNullX(t *testing.T) {
1234 t.Parallel()
1235
1236 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1237 defer closeConn(t, conn)
1238
1239 type row struct {
1240 boolValid sql.NullBool
1241 boolNull sql.NullBool
1242 int64Valid sql.NullInt64
1243 int64Null sql.NullInt64
1244 float64Valid sql.NullFloat64
1245 float64Null sql.NullFloat64
1246 stringValid sql.NullString
1247 stringNull sql.NullString
1248 }
1249
1250 expected := row{
1251 boolValid: sql.NullBool{Bool: true, Valid: true},
1252 int64Valid: sql.NullInt64{Int64: 123, Valid: true},
1253 float64Valid: sql.NullFloat64{Float64: 3.14, Valid: true},
1254 stringValid: sql.NullString{String: "pgx", Valid: true},
1255 }
1256
1257 var actual row
1258
1259 err := conn.QueryRow(
1260 context.Background(),
1261 "select $1::bool, $2::bool, $3::int8, $4::int8, $5::float8, $6::float8, $7::text, $8::text",
1262 expected.boolValid,
1263 expected.boolNull,
1264 expected.int64Valid,
1265 expected.int64Null,
1266 expected.float64Valid,
1267 expected.float64Null,
1268 expected.stringValid,
1269 expected.stringNull,
1270 ).Scan(
1271 &actual.boolValid,
1272 &actual.boolNull,
1273 &actual.int64Valid,
1274 &actual.int64Null,
1275 &actual.float64Valid,
1276 &actual.float64Null,
1277 &actual.stringValid,
1278 &actual.stringNull,
1279 )
1280 if err != nil {
1281 t.Fatalf("Scan failed: %v", err)
1282 }
1283
1284 if expected != actual {
1285 t.Errorf("Expected %v, but got %v", expected, actual)
1286 }
1287
1288 ensureConnValid(t, conn)
1289 }
1290
1291 func TestQueryContextSuccess(t *testing.T) {
1292 t.Parallel()
1293
1294 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1295 defer closeConn(t, conn)
1296
1297 ctx, cancelFunc := context.WithCancel(context.Background())
1298 defer cancelFunc()
1299
1300 rows, err := conn.Query(ctx, "select 42::integer")
1301 if err != nil {
1302 t.Fatal(err)
1303 }
1304
1305 var result, rowCount int
1306 for rows.Next() {
1307 err = rows.Scan(&result)
1308 if err != nil {
1309 t.Fatal(err)
1310 }
1311 rowCount++
1312 }
1313
1314 if rows.Err() != nil {
1315 t.Fatal(rows.Err())
1316 }
1317
1318 if rowCount != 1 {
1319 t.Fatalf("Expected 1 row, got %d", rowCount)
1320 }
1321 if result != 42 {
1322 t.Fatalf("Expected result 42, got %d", result)
1323 }
1324
1325 ensureConnValid(t, conn)
1326 }
1327
1328 func TestQueryContextErrorWhileReceivingRows(t *testing.T) {
1329 t.Parallel()
1330
1331 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1332 defer closeConn(t, conn)
1333
1334 pgxtest.SkipCockroachDB(t, conn, "Server uses numeric instead of int")
1335
1336 ctx, cancelFunc := context.WithCancel(context.Background())
1337 defer cancelFunc()
1338
1339 rows, err := conn.Query(ctx, "select 10/(10-n) from generate_series(1, 100) n")
1340 if err != nil {
1341 t.Fatal(err)
1342 }
1343
1344 var result, rowCount int
1345 for rows.Next() {
1346 err = rows.Scan(&result)
1347 if err != nil {
1348 t.Fatal(err)
1349 }
1350 rowCount++
1351 }
1352
1353 if rows.Err() == nil || rows.Err().Error() != "ERROR: division by zero (SQLSTATE 22012)" {
1354 t.Fatalf("Expected division by zero error, but got %v", rows.Err())
1355 }
1356
1357 if rowCount != 9 {
1358 t.Fatalf("Expected 9 rows, got %d", rowCount)
1359 }
1360 if result != 10 {
1361 t.Fatalf("Expected result 10, got %d", result)
1362 }
1363
1364 ensureConnValid(t, conn)
1365 }
1366
1367 func TestQueryRowContextSuccess(t *testing.T) {
1368 t.Parallel()
1369
1370 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1371 defer closeConn(t, conn)
1372
1373 ctx, cancelFunc := context.WithCancel(context.Background())
1374 defer cancelFunc()
1375
1376 var result int
1377 err := conn.QueryRow(ctx, "select 42::integer").Scan(&result)
1378 if err != nil {
1379 t.Fatal(err)
1380 }
1381 if result != 42 {
1382 t.Fatalf("Expected result 42, got %d", result)
1383 }
1384
1385 ensureConnValid(t, conn)
1386 }
1387
1388 func TestQueryRowContextErrorWhileReceivingRow(t *testing.T) {
1389 t.Parallel()
1390
1391 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1392 defer closeConn(t, conn)
1393
1394 ctx, cancelFunc := context.WithCancel(context.Background())
1395 defer cancelFunc()
1396
1397 var result int
1398 err := conn.QueryRow(ctx, "select 10/0").Scan(&result)
1399 if err == nil || err.Error() != "ERROR: division by zero (SQLSTATE 22012)" {
1400 t.Fatalf("Expected division by zero error, but got %v", err)
1401 }
1402
1403 ensureConnValid(t, conn)
1404 }
1405
1406 func TestQueryCloseBefore(t *testing.T) {
1407 t.Parallel()
1408
1409 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1410 closeConn(t, conn)
1411
1412 _, err := conn.Query(context.Background(), "select 1")
1413 require.Error(t, err)
1414 assert.True(t, pgconn.SafeToRetry(err))
1415 }
1416
1417 func TestScanRow(t *testing.T) {
1418 t.Parallel()
1419
1420 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1421 defer closeConn(t, conn)
1422
1423 resultReader := conn.PgConn().ExecParams(context.Background(), "select generate_series(1,$1)", [][]byte{[]byte("10")}, nil, nil, nil)
1424
1425 var sum, rowCount int32
1426
1427 for resultReader.NextRow() {
1428 var n int32
1429 err := pgx.ScanRow(conn.TypeMap(), resultReader.FieldDescriptions(), resultReader.Values(), &n)
1430 assert.NoError(t, err)
1431 sum += n
1432 rowCount++
1433 }
1434
1435 _, err := resultReader.Close()
1436
1437 require.NoError(t, err)
1438 assert.EqualValues(t, 10, rowCount)
1439 assert.EqualValues(t, 55, sum)
1440 }
1441
1442 func TestConnSimpleProtocol(t *testing.T) {
1443 t.Parallel()
1444
1445 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1446 defer closeConn(t, conn)
1447
1448
1449
1450 {
1451 expected := int64(42)
1452 var actual int64
1453 err := conn.QueryRow(
1454 context.Background(),
1455 "select $1::int8",
1456 pgx.QueryExecModeSimpleProtocol,
1457 expected,
1458 ).Scan(&actual)
1459 if err != nil {
1460 t.Error(err)
1461 }
1462 if expected != actual {
1463 t.Errorf("expected %v got %v", expected, actual)
1464 }
1465 }
1466
1467 {
1468 expected := float64(1.23)
1469 var actual float64
1470 err := conn.QueryRow(
1471 context.Background(),
1472 "select $1::float8",
1473 pgx.QueryExecModeSimpleProtocol,
1474 expected,
1475 ).Scan(&actual)
1476 if err != nil {
1477 t.Error(err)
1478 }
1479 if expected != actual {
1480 t.Errorf("expected %v got %v", expected, actual)
1481 }
1482 }
1483
1484 {
1485 expected := true
1486 var actual bool
1487 err := conn.QueryRow(
1488 context.Background(),
1489 "select $1::boolean",
1490 pgx.QueryExecModeSimpleProtocol,
1491 expected,
1492 ).Scan(&actual)
1493 if err != nil {
1494 t.Error(err)
1495 }
1496 if expected != actual {
1497 t.Errorf("expected %v got %v", expected, actual)
1498 }
1499 }
1500
1501 {
1502 expected := []byte{0, 1, 20, 35, 64, 80, 120, 3, 255, 240, 128, 95}
1503 var actual []byte
1504 err := conn.QueryRow(
1505 context.Background(),
1506 "select $1::bytea",
1507 pgx.QueryExecModeSimpleProtocol,
1508 expected,
1509 ).Scan(&actual)
1510 if err != nil {
1511 t.Error(err)
1512 }
1513 if !bytes.Equal(actual, expected) {
1514 t.Errorf("expected %v got %v", expected, actual)
1515 }
1516 }
1517
1518 {
1519 expected := "test"
1520 var actual string
1521 err := conn.QueryRow(
1522 context.Background(),
1523 "select $1::text",
1524 pgx.QueryExecModeSimpleProtocol,
1525 expected,
1526 ).Scan(&actual)
1527 if err != nil {
1528 t.Error(err)
1529 }
1530 if expected != actual {
1531 t.Errorf("expected %v got %v", expected, actual)
1532 }
1533 }
1534
1535 {
1536 tests := []struct {
1537 expected []string
1538 }{
1539 {[]string(nil)},
1540 {[]string{}},
1541 {[]string{"test", "foo", "bar"}},
1542 {[]string{`foo'bar"\baz;quz`, `foo'bar"\baz;quz`}},
1543 }
1544 for i, tt := range tests {
1545 var actual []string
1546 err := conn.QueryRow(
1547 context.Background(),
1548 "select $1::text[]",
1549 pgx.QueryExecModeSimpleProtocol,
1550 tt.expected,
1551 ).Scan(&actual)
1552 assert.NoErrorf(t, err, "%d", i)
1553 assert.Equalf(t, tt.expected, actual, "%d", i)
1554 }
1555 }
1556
1557 {
1558 tests := []struct {
1559 expected []int16
1560 }{
1561 {[]int16(nil)},
1562 {[]int16{}},
1563 {[]int16{1, 2, 3}},
1564 }
1565 for i, tt := range tests {
1566 var actual []int16
1567 err := conn.QueryRow(
1568 context.Background(),
1569 "select $1::smallint[]",
1570 pgx.QueryExecModeSimpleProtocol,
1571 tt.expected,
1572 ).Scan(&actual)
1573 assert.NoErrorf(t, err, "%d", i)
1574 assert.Equalf(t, tt.expected, actual, "%d", i)
1575 }
1576 }
1577
1578 {
1579 tests := []struct {
1580 expected []int32
1581 }{
1582 {[]int32(nil)},
1583 {[]int32{}},
1584 {[]int32{1, 2, 3}},
1585 }
1586 for i, tt := range tests {
1587 var actual []int32
1588 err := conn.QueryRow(
1589 context.Background(),
1590 "select $1::int[]",
1591 pgx.QueryExecModeSimpleProtocol,
1592 tt.expected,
1593 ).Scan(&actual)
1594 assert.NoErrorf(t, err, "%d", i)
1595 assert.Equalf(t, tt.expected, actual, "%d", i)
1596 }
1597 }
1598
1599 {
1600 tests := []struct {
1601 expected []int64
1602 }{
1603 {[]int64(nil)},
1604 {[]int64{}},
1605 {[]int64{1, 2, 3}},
1606 }
1607 for i, tt := range tests {
1608 var actual []int64
1609 err := conn.QueryRow(
1610 context.Background(),
1611 "select $1::bigint[]",
1612 pgx.QueryExecModeSimpleProtocol,
1613 tt.expected,
1614 ).Scan(&actual)
1615 assert.NoErrorf(t, err, "%d", i)
1616 assert.Equalf(t, tt.expected, actual, "%d", i)
1617 }
1618 }
1619
1620 {
1621 tests := []struct {
1622 expected []int
1623 }{
1624 {[]int(nil)},
1625 {[]int{}},
1626 {[]int{1, 2, 3}},
1627 }
1628 for i, tt := range tests {
1629 var actual []int
1630 err := conn.QueryRow(
1631 context.Background(),
1632 "select $1::bigint[]",
1633 pgx.QueryExecModeSimpleProtocol,
1634 tt.expected,
1635 ).Scan(&actual)
1636 assert.NoErrorf(t, err, "%d", i)
1637 assert.Equalf(t, tt.expected, actual, "%d", i)
1638 }
1639 }
1640
1641 {
1642 tests := []struct {
1643 expected []uint16
1644 }{
1645 {[]uint16(nil)},
1646 {[]uint16{}},
1647 {[]uint16{1, 2, 3}},
1648 }
1649 for i, tt := range tests {
1650 var actual []uint16
1651 err := conn.QueryRow(
1652 context.Background(),
1653 "select $1::smallint[]",
1654 pgx.QueryExecModeSimpleProtocol,
1655 tt.expected,
1656 ).Scan(&actual)
1657 assert.NoErrorf(t, err, "%d", i)
1658 assert.Equalf(t, tt.expected, actual, "%d", i)
1659 }
1660 }
1661
1662 {
1663 tests := []struct {
1664 expected []uint32
1665 }{
1666 {[]uint32(nil)},
1667 {[]uint32{}},
1668 {[]uint32{1, 2, 3}},
1669 }
1670 for i, tt := range tests {
1671 var actual []uint32
1672 err := conn.QueryRow(
1673 context.Background(),
1674 "select $1::bigint[]",
1675 pgx.QueryExecModeSimpleProtocol,
1676 tt.expected,
1677 ).Scan(&actual)
1678 assert.NoErrorf(t, err, "%d", i)
1679 assert.Equalf(t, tt.expected, actual, "%d", i)
1680 }
1681 }
1682
1683 {
1684 tests := []struct {
1685 expected []uint64
1686 }{
1687 {[]uint64(nil)},
1688 {[]uint64{}},
1689 {[]uint64{1, 2, 3}},
1690 }
1691 for i, tt := range tests {
1692 var actual []uint64
1693 err := conn.QueryRow(
1694 context.Background(),
1695 "select $1::bigint[]",
1696 pgx.QueryExecModeSimpleProtocol,
1697 tt.expected,
1698 ).Scan(&actual)
1699 assert.NoErrorf(t, err, "%d", i)
1700 assert.Equalf(t, tt.expected, actual, "%d", i)
1701 }
1702 }
1703
1704 {
1705 tests := []struct {
1706 expected []uint
1707 }{
1708 {[]uint(nil)},
1709 {[]uint{}},
1710 {[]uint{1, 2, 3}},
1711 }
1712 for i, tt := range tests {
1713 var actual []uint
1714 err := conn.QueryRow(
1715 context.Background(),
1716 "select $1::bigint[]",
1717 pgx.QueryExecModeSimpleProtocol,
1718 tt.expected,
1719 ).Scan(&actual)
1720 assert.NoErrorf(t, err, "%d", i)
1721 assert.Equalf(t, tt.expected, actual, "%d", i)
1722 }
1723 }
1724
1725 {
1726 tests := []struct {
1727 expected []float32
1728 }{
1729 {[]float32(nil)},
1730 {[]float32{}},
1731 {[]float32{1, 2, 3}},
1732 }
1733 for i, tt := range tests {
1734 var actual []float32
1735 err := conn.QueryRow(
1736 context.Background(),
1737 "select $1::float4[]",
1738 pgx.QueryExecModeSimpleProtocol,
1739 tt.expected,
1740 ).Scan(&actual)
1741 assert.NoErrorf(t, err, "%d", i)
1742 assert.Equalf(t, tt.expected, actual, "%d", i)
1743 }
1744 }
1745
1746 {
1747 tests := []struct {
1748 expected []float64
1749 }{
1750 {[]float64(nil)},
1751 {[]float64{}},
1752 {[]float64{1, 2, 3}},
1753 }
1754 for i, tt := range tests {
1755 var actual []float64
1756 err := conn.QueryRow(
1757 context.Background(),
1758 "select $1::float8[]",
1759 pgx.QueryExecModeSimpleProtocol,
1760 tt.expected,
1761 ).Scan(&actual)
1762 assert.NoErrorf(t, err, "%d", i)
1763 assert.Equalf(t, tt.expected, actual, "%d", i)
1764 }
1765 }
1766
1767
1768
1769 {
1770 if conn.PgConn().ParameterStatus("crdb_version") == "" {
1771
1772 expected := pgtype.Circle{P: pgtype.Vec2{X: 1, Y: 2}, R: 1.5, Valid: true}
1773 actual := expected
1774 err := conn.QueryRow(
1775 context.Background(),
1776 "select $1::circle",
1777 pgx.QueryExecModeSimpleProtocol,
1778 &expected,
1779 ).Scan(&actual)
1780 if err != nil {
1781 t.Error(err)
1782 }
1783 if expected != actual {
1784 t.Errorf("expected %v got %v", expected, actual)
1785 }
1786 }
1787 }
1788
1789
1790
1791 {
1792 expectedInt64 := int64(234423)
1793 expectedFloat64 := float64(-0.2312)
1794 expectedBool := true
1795 expectedBytes := []byte{255, 0, 23, 16, 87, 45, 9, 23, 45, 223}
1796 expectedString := "test"
1797 var actualInt64 int64
1798 var actualFloat64 float64
1799 var actualBool bool
1800 var actualBytes []byte
1801 var actualString string
1802 err := conn.QueryRow(
1803 context.Background(),
1804 "select $1::int8, $2::float8, $3::boolean, $4::bytea, $5::text",
1805 pgx.QueryExecModeSimpleProtocol,
1806 expectedInt64, expectedFloat64, expectedBool, expectedBytes, expectedString,
1807 ).Scan(&actualInt64, &actualFloat64, &actualBool, &actualBytes, &actualString)
1808 if err != nil {
1809 t.Error(err)
1810 }
1811 if expectedInt64 != actualInt64 {
1812 t.Errorf("expected %v got %v", expectedInt64, actualInt64)
1813 }
1814 if expectedFloat64 != actualFloat64 {
1815 t.Errorf("expected %v got %v", expectedFloat64, actualFloat64)
1816 }
1817 if expectedBool != actualBool {
1818 t.Errorf("expected %v got %v", expectedBool, actualBool)
1819 }
1820 if !bytes.Equal(expectedBytes, actualBytes) {
1821 t.Errorf("expected %v got %v", expectedBytes, actualBytes)
1822 }
1823 if expectedString != actualString {
1824 t.Errorf("expected %v got %v", expectedString, actualString)
1825 }
1826 }
1827
1828
1829
1830 {
1831 expected := "foo';drop table users;"
1832 var actual string
1833 err := conn.QueryRow(
1834 context.Background(),
1835 "select $1",
1836 pgx.QueryExecModeSimpleProtocol,
1837 expected,
1838 ).Scan(&actual)
1839 if err != nil {
1840 t.Error(err)
1841 }
1842 if expected != actual {
1843 t.Errorf("expected %v got %v", expected, actual)
1844 }
1845 }
1846
1847 ensureConnValid(t, conn)
1848 }
1849
1850 func TestConnSimpleProtocolRefusesNonUTF8ClientEncoding(t *testing.T) {
1851 t.Parallel()
1852
1853 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1854 defer closeConn(t, conn)
1855
1856 pgxtest.SkipCockroachDB(t, conn, "Server does not support changing client_encoding (https://www.cockroachlabs.com/docs/stable/set-vars.html)")
1857
1858 mustExec(t, conn, "set client_encoding to 'SQL_ASCII'")
1859
1860 var expected string
1861 err := conn.QueryRow(
1862 context.Background(),
1863 "select $1",
1864 pgx.QueryExecModeSimpleProtocol,
1865 "test",
1866 ).Scan(&expected)
1867 if err == nil {
1868 t.Error("expected error when client_encoding not UTF8, but no error occurred")
1869 }
1870
1871 ensureConnValid(t, conn)
1872 }
1873
1874 func TestConnSimpleProtocolRefusesNonStandardConformingStrings(t *testing.T) {
1875 t.Parallel()
1876
1877 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1878 defer closeConn(t, conn)
1879
1880 pgxtest.SkipCockroachDB(t, conn, "Server does not support standard_conforming_strings = off (https://github.com/cockroachdb/cockroach/issues/36215)")
1881
1882 mustExec(t, conn, "set standard_conforming_strings to off")
1883
1884 var expected string
1885 err := conn.QueryRow(
1886 context.Background(),
1887 "select $1",
1888 pgx.QueryExecModeSimpleProtocol,
1889 `\'; drop table users; --`,
1890 ).Scan(&expected)
1891 if err == nil {
1892 t.Error("expected error when standard_conforming_strings is off, but no error occurred")
1893 }
1894
1895 ensureConnValid(t, conn)
1896 }
1897
1898
1899 func TestQueryErrorWithDisabledStatementCache(t *testing.T) {
1900 t.Parallel()
1901
1902 config := mustParseConfig(t, os.Getenv("PGX_TEST_DATABASE"))
1903 config.DefaultQueryExecMode = pgx.QueryExecModeDescribeExec
1904 config.StatementCacheCapacity = 0
1905 config.DescriptionCacheCapacity = 0
1906
1907 conn := mustConnect(t, config)
1908 defer closeConn(t, conn)
1909
1910 _, err := conn.Exec(context.Background(), "create temporary table t_unq(id text primary key);")
1911 require.NoError(t, err)
1912
1913 _, err = conn.Exec(context.Background(), "insert into t_unq (id) values ($1)", "abc")
1914 require.NoError(t, err)
1915
1916 rows, err := conn.Query(context.Background(), "insert into t_unq (id) values ($1)", "abc")
1917 require.NoError(t, err)
1918 rows.Close()
1919 err = rows.Err()
1920 require.Error(t, err)
1921 var pgErr *pgconn.PgError
1922 if errors.As(err, &pgErr) {
1923 assert.Equal(t, "23505", pgErr.Code)
1924 } else {
1925 t.Errorf("err is not a *pgconn.PgError: %T", err)
1926 }
1927
1928 ensureConnValid(t, conn)
1929 }
1930
1931 func TestConnQueryQueryExecModeCacheDescribeSafeEvenWhenTypesChange(t *testing.T) {
1932 t.Parallel()
1933
1934 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
1935 defer cancel()
1936
1937 conn := mustConnectString(t, os.Getenv("PGX_TEST_DATABASE"))
1938 defer closeConn(t, conn)
1939
1940 pgxtest.SkipCockroachDB(t, conn, "Server does not support alter column type from int to float4")
1941
1942 _, err := conn.Exec(ctx, `create temporary table to_change (
1943 name text primary key,
1944 age int
1945 );
1946
1947 insert into to_change (name, age) values ('John', 42);`)
1948 require.NoError(t, err)
1949
1950 var name string
1951 var ageInt32 int32
1952 err = conn.QueryRow(ctx, "select * from to_change where age = $1", pgx.QueryExecModeCacheDescribe, int32(42)).Scan(&name, &ageInt32)
1953 require.NoError(t, err)
1954 require.Equal(t, "John", name)
1955 require.Equal(t, int32(42), ageInt32)
1956
1957 _, err = conn.Exec(ctx, `alter table to_change alter column age type float4;`)
1958 require.NoError(t, err)
1959
1960 err = conn.QueryRow(ctx, "select * from to_change where age = $1", pgx.QueryExecModeCacheDescribe, int32(42)).Scan(&name, &ageInt32)
1961 require.NoError(t, err)
1962 require.Equal(t, "John", name)
1963 require.Equal(t, int32(42), ageInt32)
1964
1965 var ageFloat32 float32
1966 err = conn.QueryRow(ctx, "select * from to_change where age = $1", pgx.QueryExecModeCacheDescribe, int32(42)).Scan(&name, &ageFloat32)
1967 require.NoError(t, err)
1968 require.Equal(t, "John", name)
1969 require.Equal(t, float32(42), ageFloat32)
1970
1971 _, err = conn.Exec(ctx, `alter table to_change drop column name;`)
1972 require.NoError(t, err)
1973
1974
1975 err = conn.QueryRow(ctx, "select * from to_change where age = $1", pgx.QueryExecModeCacheDescribe, int32(42)).Scan(&ageFloat32)
1976 require.EqualError(t, err, "ERROR: bind message has 2 result formats but query has 1 columns (SQLSTATE 08P01)")
1977
1978
1979 err = conn.QueryRow(ctx, "select * from to_change where age = $1", pgx.QueryExecModeCacheDescribe, int32(42)).Scan(&ageFloat32)
1980 require.NoError(t, err)
1981 require.Equal(t, float32(42), ageFloat32)
1982
1983 _, err = conn.Exec(ctx, `alter table to_change alter column age type numeric;`)
1984 require.NoError(t, err)
1985
1986 err = conn.QueryRow(ctx, "select * from to_change where age = $1", pgx.QueryExecModeCacheDescribe, int32(42)).Scan(&ageFloat32)
1987 require.NoError(t, err)
1988 require.Equal(t, float32(42), ageFloat32)
1989 }
1990
1991 func TestQueryWithQueryRewriter(t *testing.T) {
1992 t.Parallel()
1993
1994 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
1995 defer cancel()
1996
1997 pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
1998 qr := testQueryRewriter{sql: "select $1::int", args: []any{42}}
1999 rows, err := conn.Query(ctx, "should be replaced", &qr)
2000 require.NoError(t, err)
2001
2002 var n int32
2003 var rowCount int
2004 for rows.Next() {
2005 rowCount++
2006 err = rows.Scan(&n)
2007 require.NoError(t, err)
2008 }
2009
2010 require.NoError(t, rows.Err())
2011 })
2012 }
2013
2014
2015
2016 func ExampleConn_Query() {
2017 ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
2018 defer cancel()
2019
2020 conn, err := pgx.Connect(ctx, os.Getenv("PGX_TEST_DATABASE"))
2021 if err != nil {
2022 fmt.Printf("Unable to establish connection: %v", err)
2023 return
2024 }
2025
2026 if conn.PgConn().ParameterStatus("crdb_version") != "" {
2027
2028 fmt.Println(`Cheeseburger: $10
2029 Fries: $5
2030 Soft Drink: $3`)
2031 return
2032 }
2033
2034
2035 _, err = conn.Exec(ctx, `
2036 create temporary table products (
2037 id int primary key generated by default as identity,
2038 name varchar(100) not null,
2039 price int not null
2040 );
2041
2042 insert into products (name, price) values
2043 ('Cheeseburger', 10),
2044 ('Double Cheeseburger', 14),
2045 ('Fries', 5),
2046 ('Soft Drink', 3);
2047 `)
2048 if err != nil {
2049 fmt.Printf("Unable to setup example schema and data: %v", err)
2050 return
2051 }
2052
2053 rows, err := conn.Query(ctx, "select name, price from products where price < $1 order by price desc", 12)
2054
2055
2056
2057 if err != nil {
2058 fmt.Printf("Query error: %v", err)
2059 return
2060 }
2061
2062
2063 defer rows.Close()
2064
2065
2066 for rows.Next() {
2067 var name string
2068 var price int32
2069
2070 err = rows.Scan(&name, &price)
2071 if err != nil {
2072 fmt.Printf("Scan error: %v", err)
2073 return
2074 }
2075
2076 fmt.Printf("%s: $%d\n", name, price)
2077 }
2078
2079
2080
2081
2082 if rows.Err() != nil {
2083 fmt.Printf("rows error: %v", rows.Err())
2084 return
2085 }
2086
2087
2088
2089
2090
2091 }
2092
View as plain text