How to create a table in Spanner using PostgreSQL dialect that has a SPANNER.COMMIT_TIMESTAMP column?

Question

I am running the query:

CREATE TABLE dump_table (
  id character varying NOT NULL,
  project_id character varying NOT NULL,
  user_id character varying NOT NULL,
  label character varying NOT NULL,
  value character varying NOT NULL,
  created_on timestamp with time zone NOT NULL,
  updated_on SPANNER.COMMIT_TIMESTAMP with time zone NOT NULL,
  deleted_on timestamp with time zone,
  PRIMARY KEY(id, project_id)
);;

It is my intention to not allow null values for columns: created_on and updated_on. Moreover, I want to commit to updated_on using this Golang code:

mutation := spanner.InsertOrUpdate(
        "pinectl_dump",
        []string{"id", "project_id", "user_id", "label", "value", "environment", "created_on", "updated_on", "deleted_on"},
        []interface{}{apiKey, projectID, userID, userLabel, apiKey, environment, creationTimestamp, spanner.CommitTimestamp, nil},
    )

I read the official documentation that shows an example to create a table with such a column but when I execute (try to) the query, it fails with the following error:

Expected "timezone"i

When I remove the created_on row from the query, I get the error on the next one:

Expected "ARRAY<"i, "bigint"i, "bool"i, "boolean"i, "bytea"i, "character varying"i, "character"i, "date"i, "decimal"i, "double precision"i, "double"i, "float8"i, "int"i, "int8"i, "jsonb"i, "numeric"i, "text"i, "timestamp with timezone"i, "timestamp"i, "timestamptz"i or "varchar"i.

How do I create a Spanner table using PostgreSQL dialect such that one column from it can be used to log updated_on timestamp with timezone?

Answer 1

The problem is the with time zone suffix after the SPANNER.COMMIT_TIMESTAMP type specification. SPANNER.COMMIT_TIMESTAMP is by definition always with a timezone, and the the with time zone specification is therefore not needed and also not supported. The following should therefore work:

CREATE TABLE dump_table (
  id character varying NOT NULL,
  project_id character varying NOT NULL,
  user_id character varying NOT NULL,
  label character varying NOT NULL,
  value character varying NOT NULL,
  created_on timestamp with time zone NOT NULL,
  updated_on SPANNER.COMMIT_TIMESTAMP NOT NULL,
  deleted_on timestamp with time zone,
  PRIMARY KEY(id, project_id)
);