Cannot "Scan" datetime from PostgreSQL

[ThePix]

I want to use a PostgreSQL database from a Go webserver, and as a first step I just want basic access to the database. I can create a table, and add a record, but on retrieval f said record, Go chokes on the timestamp.

2023/05/24 14:15:36 sql: Scan error on column index 3, name “created_at”: unsupported Scan, storing driver.Value type into type *time.Time

This is the line it is failing at.

        if err := db.QueryRow(query, 1).Scan(&id, &username, &password, &createdAt); err != nil {

I have found mention of an issue with MySQL that involves setting something in the connection string ( parseTime=true), but nothing for Postgres. I am only just starting out with Go, and most of my code is copied from elsewhere - and that uses MySQL.

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/lib/pq"
)

const (
  host     = "localhost"
  port     = 5432
  user     = "postgres"
  password = "*******"
  dbname   = "com_dev"
)


func main() {

    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
      "password=%s dbname=%s sslmode=disable",
      host, port, user, password, dbname)
    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        log.Fatal(err)
    }
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }

    { // Create a new table
        fmt.Println("Trying to create user table")
        query := `
            CREATE TABLE users (
                id SERIAL PRIMARY KEY,
                username TEXT NOT NULL,
                password TEXT NOT NULL,
                created_at TIMESTAMP
            );`

        if _, err := db.Exec(query); err != nil {
            fmt.Println(err) 
            //log.Fatal(err)
        }
    }

    { // Insert a new user
        fmt.Println("Trying to add user")
        username := "johndoe"
        password := "secret"
        createdAt := time.Now()

        result, err := db.Exec("INSERT INTO users (username, password, created_at) VALUES ($1, $2, $3)", username, password, createdAt)
        if err != nil {
            fmt.Println("add failed")
            log.Fatal(err)
        }

        id, err := result.LastInsertId()
        fmt.Println(id)
    }

    { // Query a single user
        fmt.Println("Trying to find user")
        var (
            id        int
            username  string
            password  string
            createdAt time.Time
        )

        query := "SELECT id, username, password, created_at FROM users WHERE id = $1"
        if err := db.QueryRow(query, 1).Scan(&id, &username, &password, &createdAt); err != nil {
            log.Fatal(err)
        }

        fmt.Println(id, username, password, createdAt)
    }
}

[Sibert]

@ThePix

        query := `
            CREATE TABLE users (
                id SERIAL PRIMARY KEY,
                username TEXT NOT NULL,
                password TEXT NOT NULL,
                created_at TIMESTAMP
            );`

But why don’t you let Postgresql create the timestamp automatically?

created_at TIMESTAMP default CURRENT_TIMESTAMP,

…and can you see the timestamp in pgAdmin4. Is it stored?

[Dean_Davidson]

Interesting. The problem isn’t immediately obvious to me. What happens if you change the following?

// Current
query := "SELECT id, username, password, created_at FROM users WHERE id = $1"
// Hard-code time.Time column for testing
query := "SELECT id, username, password, now() FROM users WHERE id = $1"

It’s problematic to me that the error doesn’t know the source type. That error usually says “type someType into…” but it’s missing in your case. I also noticed you’re using /lib/pq which is in maintenance mode. From their readme:

For users that require new features or reliable resolution of reported bugs, we recommend using pgx which is under active development.

I personally use pgx for my Postgres projects. I can’t imagine that something as basic as scanning to time.Time is broken in that projects, but I wonder if you swapped out drivers if it would just work? To keep it using database/sql interface, check this pgx guide out:

Getting started with pgx through database sql

Short version is: swap out your driver in your imports, and instead of “postgres” as the driver name in sql.Open use “pgx”.

Finally, if you just query the database directly, what does that row look like?

[ThePix]

I copy-and-posted the code from a tutorial that does it that way. It used MySQL; perhaps timestamp is not built is? Or it wanted to illustrate the point?

[Sibert]

@ThePix

It used MySQL

I do know about MySQL, but I think it is convenient to let the database create the timestamp.

But question is if the timestamp is stored in the database? Do you use pgAdmin4 in order to “manage” the database? Highly recommended for tracking down issues. There is a built in query tool.

And I agree with @ Dean_Davidson that “pgx” is a good choice.

[ThePix]

I have swapped to pgx… Same result.

However, it did prompt me to delete the table in pgAdmin, and when I ran it again, it worked, so I think the issue was bad data in the database.

Thanks for the help, it got me going the right way!