I want to create a table with computed column.
I wish to use current_date as value in one of my columns named ‘l_date’
but I am getting error. Here is my DDL:
create table comp_col(
id varchar(10),
name varchar(10),
l_date as current_date PERSISTED timestamp
);
ERROR:
ERROR 1825 ER_MEMSQL_COMPUTED_IS_TIMESTAMP: Cannot create computed column ‘l_date’ of type TIMESTAMP
Persisted computed columns have to be set to deterministic expressions of one or more other columns. Using the current time in a computed column expression is non-deterministic and thus is not supported.
If you want to use the current time value in a field when you insert or update the record, you can use something like:
SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘6) default current_timestamp(6)¶¶ KEY col_key (a) USING CLUSTERED COLUMNSTORE’ at line 1
SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6) default current_timestamp(6),¶¶KEY col_key (a) USING CLUSTERED COLUMNSTORE ’ at line 1
Are you using a version older than 7.3?
current_timestamp(6) didn’t used to be supported for ON UPDATE or DEFAULT until a recent release. Can’t remember which one. If you don’t care about fractions, CURRENT_TIMESTAMP might work for you.
I am using a older version (6.8)
If it is not possible in this version, what is an alternative for this ? can you please help me with it?
Also, can you please give you an example to create a table with computed column as current_date or CURRENT_TIMESTAMP
Try CURRENT_TIMESTAMP or NOW(). I don’t have solution for you for a column defaulting to current_date. Consider having a datetime column default to current_date and create a computed column of type date from that, or just use the datetime column in your app. That works in 7.3:
memsql> create table t (a datetime default current_timestamp, b as a persisted date);
Query OK, 0 rows affected (0.07 sec)
memsql> insert t values();
Query OK, 1 row affected (0.05 sec)
memsql> select * from t;
+---------------------+------------+
| a | b |
+---------------------+------------+
| 2021-02-24 17:49:33 | 2021-02-24 |
+---------------------+------------+
I am trying to calculate AGE from a AES_ENCRYPTED column which contains the Birthday . But I am facing the below similar issue. Can u suggest any work around for this
ALTER TABLE XYZ ADD TEST_VAL AS (TIMESTAMPDIFF(YEAR, cast(AES_DECRYPT(BIRTHDAY,‘secret_key’) as char), CURDATE())) PERSISTED INT;
ERROR 1824 (HY000): Forwarding Error (ip:port): Cannot create computed column ‘TEST_VAL’ that depends on date/time function
mysql>
Hi Ann. Any persisted computed column has to be derived only from literals and other regular columns. CURDATE() is a non-deterministic function that returns the current date. So you can’t use it in a persisted computed column expression. Consider inserting CURDATE() into some other column and then making your persisted computed column a function of that other column.