GCP Spanner support array of structs

Question

Does Spanner support array of structs?

The following DDL generates errors:

CREATE TABLE table1 (
    col1 BYTES(MAX) NOT NULL,
    col2 STRING(36) NOT NULL,
    col3 TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
    col4 TIMESTAMP NOT NULL,
     labels array<struct<
      name STRING NOT NULL,
      last_updated timestatmp NOT NULL>>, 
    col5 INT64 NOT NULL,
    col6 STRING(36) NOT NULL,
    col7 STRING(36) NOT NULL,
) PRIMARY KEY (col1);

Error parsing Spanner DDL statement: CREATE TABLE table1 ( col1 BYTES(MAX) NOT NULL, col2 STRING(36) NOT NULL, col3 TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true), col4 TIMESTAMP NOT NULL, labels array>, col5 INT64 NOT NULL, col6 STRING(36) NOT NULL, col7 STRING(36) NOT NULL, ) PRIMARY KEY (col1) : Syntax error on line 6, column 19: Encountered 'struct' while parsing: column_type

of course, removing the labels array of structs column works fine.

I looked up spanner datatypes and it appears the "struct" dt cannot be a column datatype. Would appreciate if someone can confirm. — Dave

Yang Song Yang Song · Accepted Answer · 2020-06-11T23:30:24

Correct. In Spanner ARRAY of STRUCTs values can be constructed by SQL expressions, but are not supported as column types. See https://cloud.google.com/spanner/docs/data-types#declaring_an_array_type.

GCP Spanner support array of structs

1 Answers