current_date/current_time Keywords As Field Name

Availability

This feature is available since MogDB 5.0.0.

Introduction

Supports current_date/current_time as table field names for operations.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Description

Current_date and current_time are used as system functions to return the current system date and system time in the database and cannot be used as table field names as RESERVED_KEYWORD keywords. This feature supports the operation of current_date/current_time as table field names for compatibility with application development.

Constraints

Currently only current_date/current_time are supported as field names in CREATE TABLE successfully;
Other DML operations, such as SELECT/DELETE/UPDATE due to ambiguities with the current_date system function, the operation is not supported. (Related operations need to add double quotes to deal with)
Currently current_date/current_time is still defined as a system function rather than a field in the check constraints, and is recognized as a field with double quotes.

Example

-- CREATE TABLE
MogDB=# CREATE TABLE test_date(current_date date);
CREATE TABLE
MogDB=# CREATE TABLE test_time(current_time time);
CREATE TABLE

-- UPDATE
MogDB=# UPDATE test SET current_date = '2022-12-14';
UPDATE 1


MogDB=# CREATE TABLE test(current_date date);
CREATE TABLE
MogDB=# INSERT INTO test VALUES ('2022-12-15');
INSERT 0 1
MogDB=# SELECT * FROM test;
    current_date     
---------------------
 2022-12-15 00:00:00
(1 row)

-- The SELECT operation returns the current date or time as a system function. Field operations require double quotes.
MogDB=# SELECT current_date FROM test;
    date    
------------
 2022-12-16      
(1 row)

MogDB=# SELECT "current_date" FROM test;
    current_date     
---------------------
 2022-12-15 00:00:00     Note: Double quotes are added to display the field value date.
(1 row)
                           
-- The DELETE operation defaults to being a system function operation, and field operations require double quotes.
MogDB=# DELETE FROM test WHERE CURRENT_DATE = '2022-12-14';
DELETE 0
MogDB=# SELECT * FROM test;
    current_date     
---------------------
 2022-12-14 00:00:00
(1 row)
MogDB=# DELETE FROM test WHERE "current_date" = '2022-12-14';
DELETE 1
MogDB=# SELECT * FROM test ;
 current_date 
--------------
(0 rows)