PSQL timestamp syntax-CodePudding

I have a go function that inserts data into a table with a statement prepared and executed similar to the following:

func Insert(w http.ResponseWriter, r *http.Request) {
    db := dbConn()
    if r.Method == "POST" {
        email := r.FormValue("email")
        firstName := r.FormValue("firstName")
        lastName := r.FormValue("lastName")
        createdOn := time.Now().UTC()

        insForm, err := db.Prepare(`INSERT INTO public.users(email, firstName, lastName, created_on) VALUES ($1,$2, $3, $4)`)
        CheckError(err)
        insForm.Exec(email, firstName, lastName, createdOn)

    defer db.Close()

    http.Redirect(w, r, "/", 301)
}

The psql error returned is:

ERROR:  invalid input syntax for type timestamp: "2022-08-04 17:45:40.10189  0000 UTC"
LINE 1: ...) VALUES ('[email protected]', 'test', 'testing', '2022-08-0...`

dbConn is defined as:

   func dbConn() (db *sql.DB) {
    // pass the db credentials into variables
    host := goDotEnvVariable("DBHOST")
    port := goDotEnvVariable("DBPORT")
    dbUser := goDotEnvVariable("DBUSER")
    dbPass := goDotEnvVariable("DBPASS")
    dbname := goDotEnvVariable("DBNAME")
    // create a connection string
    psqlInfo := fmt.Sprintf("host=%s port=%s user=%s " 
        "password=%s dbname=%s sslmode=disable",
        host, port, dbUser, dbPass, dbname)
    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        panic(err)
    }
    return db
}

What is the correct syntax to enter the timestamp with the appropriate UTC time zone?

CodePudding user response：

@eomolo Is there any reason that led you to generate the value of that column in the application instead of in the database? For fields such as created_at I like to let the database handle its generation.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CodePudding user response：

The timestamp formatting was a red herring. It was an oversite not to implement a panic after the SQL statement execution and after doing so, I was able to capture a permissions error.

        insForm, err := db.Prepare(`INSERT INTO public.users(email, firstName, lastName, created_on) VALUES ($1,$2, $3, $4)`)
        CheckError(err)
        _, err = insForm.Exec(email, firstName, lastName, createdOn)
        CheckError(err)

The problem was that while I had created a separate DB user for the application, I had not granted it privileges.

GRANT ALL PRIVILEGES ON DATABASE <db> TO <user>;

The invalid input syntax was a result of copying the log output as text directly into a statement instead of converting the time logged into timestampz datatype.