Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can not select _PARTIONTIME in SELECT but can select it in INSERT

When running the below SELECT I get this error message

Error: Invalid field name "_PARTITIONTIME". Field names are not allowed to start with the (case-insensitive) prefixes _PARTITION, TABLE, FILE and _ROW_TIMESTAMP

SELECT
    _PARTITIONTIME,
    jobId
FROM
    `project.dataset.audit`
WHERE
    _PARTITIONTIME >= TIMESTAMP("2019-02-20")

However, when I use it in a DML the query is working

INSERT INTO
  `project.dataset.audit_clustered`
  (
      _PARTITIONTIME,
      jobId,
  )
SELECT
    _PARTITIONTIME,
    jobId
FROM
    `project.dataset.audit`
WHERE
    _PARTITIONTIME >= TIMESTAMP("2019-02-20")

I have 2 questions:

  1. Why the SELECT alone is not working
  2. Is it guarantee that the INSERT will work properly and data will be inserted to the correct partition of the target table
like image 700
Tamir Klein Avatar asked Dec 14 '25 04:12

Tamir Klein


1 Answers

Replace

SELECT  _PARTITIONTIME

With

SELECT  _PARTITIONTIME AS something

This because the resulting set can't have a column starting with _, but you can name it anything else.

like image 84
Felipe Hoffa Avatar answered Dec 16 '25 04:12

Felipe Hoffa



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!