Minimal reproduction of the CrateDB TIMESTAMP WITHOUT TIME ZONE / pgx bug.

pgx_minimal.go

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgtype"
)

// Minimal reproduction of the CrateDB TIMESTAMP WITHOUT TIME ZONE / pgx bug.
// https://github.com/crate/crate/issues/19426
// 
// Init and Install pgx: go mod init pgx_minimal && go get github.com/jackc/pgx/v5 2>&1
//
// Run against CrateDB:    go run pgx_minimal.go
// Run against PostgreSQL: DATABASE_URL=postgres://postgres:postgres@localhost:5433/testdb go run pgx_minimal.go
func main() {
	ctx := context.Background()

	url := "postgres://crate@localhost:5432/doc"

	// -- Binary protocol (pgx v5 default for OID 1114) --
	conn, err := pgx.Connect(ctx, url)
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	conn.Exec(ctx, "CREATE TABLE IF NOT EXISTS ts_test (id INT, ts TIMESTAMP WITHOUT TIME ZONE)")
	conn.Exec(ctx, "INSERT INTO ts_test (id, ts) VALUES (1, '2026-05-26 12:00:00')")
	conn.Exec(ctx, "REFRESH TABLE ts_test")

	var tsBinary pgtype.Timestamp
	err = conn.QueryRow(ctx, "SELECT ts FROM ts_test").Scan(&tsBinary)
	if err != nil {
		fmt.Println("FAILED binary:", err)
	} else {
		fmt.Println("OK binary:    ", tsBinary.Time)
	}

	// -- Simple / text protocol (what Grafana's PostgreSQL datasource uses) --
	cfg, _ := pgx.ParseConfig(url)
	cfg.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
	connSimple, err := pgx.ConnectConfig(ctx, cfg)
	if err != nil {
		log.Fatal(err)
	}
	defer connSimple.Close(ctx)

	// Show the raw text value CrateDB sends over the wire
	var raw pgtype.Text
	connSimple.QueryRow(ctx, "SELECT ts FROM ts_test").Scan(&raw)
	fmt.Printf("Wire text value: %q\n", raw.String)
	// CrateDB output: "2026-05-26 12:00:00.000+00"
	// PostgreSQL output: "2026-05-26 12:00:00"  (no timezone suffix)

	// Parse as pgtype.Timestamp — fails against CrateDB, succeeds against PostgreSQL
	var tsSimple pgtype.Timestamp
	err = connSimple.QueryRow(ctx, "SELECT ts FROM ts_test").Scan(&tsSimple)
	if err != nil {
		fmt.Println("FAILED text:  ", err)
		// CrateDB: parsing time "2026-05-26 12:00:00.000+00": extra text: "+00"
	} else {
		fmt.Println("OK text:      ", tsSimple.Time)
	}
}