A case where the date field in Oracle does not define a date type

Many development specifications may write date type fields , It should be defined by the standard date type , Don't use strings 、 Numerical type, etc , Relevant historical articles , As shown below ,

《 Date field is not defined DATE Some problems caused by types 》

《 Why is the date not recommended VARCHAR2 perhaps NUMBER？》

But sometimes , For some reason , For example, heterogeneous database synchronization avoids the difference of field types , The date field will be defined as a string type , Although it meets this demand , But it may bring inconvenience to other aspects of use , The following example , It's a recent Oracle Questions raised in the technology group , Maybe a lot of friends , You'll meet .

The table contains a date data field , But defined as char Type of string , And it's partitioned , The partition field is the date of this string type , But the partition condition is according to to_date(char The type field ) To do the , As shown below ,

CREATE TABLE customer1(age NUMBER, birthday char(20))
PARTITION BY RANGE (birthday)
(
PARTITION p1 VALUES less than (to_date('2022-01','yyyy-mm')),
PARTITION p2 VALUES less than (to_date('2022-02','yyyy-mm')),
PARTITION p3 VALUES less than (to_date('2022-03','yyyy-mm'))
);

If this is the definition , It is OK to insert the first day of the month in the range ,

INSERT INTO customer1 values(1, to_date('2022-02-01','yyyy-mm-dd'));

But when inserting other days of the month in the range , You will be prompted to report an error ,"SQL error [14400] [72000]: ORA-14400: The inserted partition keyword is not mapped to any partition ",

SQL> INSERT INTO customer1 values(2, to_date('2022-02-02','yyyy-mm-dd'));
INSERT INTO customer1 values(2, to_date('2022-02-02','yyyy-mm-dd'))
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

If we define the date field as standard according to the specification DATE type ,

CREATE TABLE customer2(age NUMBER, birthday date)
PARTITION BY RANGE (birthday)
(
PARTITION p1 VALUES less than (to_date('2022-01','yyyy-mm')),
PARTITION p2 VALUES less than (to_date('2022-02','yyyy-mm')),
PARTITION p3 VALUES less than (to_date('2022-03','yyyy-mm'))
);

Insert the date that meets the partition conditions , Can be implemented ,

INSERT INTO customer2 values(1, to_date('2022-01-01','yyyy-mm-dd'));
INSERT INTO customer2 values(1, to_date('2022-02-01','yyyy-mm-dd'));
INSERT INTO customer2 values(2, to_date('2022-02-02','yyyy-mm-dd'));

As shown below ,

SQL> SELECT * FROM customer2;
AGE BIRTHDAY
---------- ------------------
1 01-JAN-22
1 01-FEB-22
         2 02-FEB-22

If the date field , Is defined as a string type , You have to partition , Create virtual columns , It's a solution , As shown below , The virtual column uses this string date field as to_date Convert to DATE The date type ,

CREATE TABLE customer3(age NUMBER, birthday char(20), partiton_key DATE GENERATED ALWAYS AS (to_date(birthday, 'yyyy-mm-dd')))
PARTITION BY RANGE (partiton_key)
(
PARTITION p1 VALUES less than (to_date('2022-01','yyyy-mm')),
PARTITION p2 VALUES less than (to_date('2022-02','yyyy-mm')),
PARTITION p3 VALUES less than (to_date('2022-03','yyyy-mm'))
);

But it brings a problem to development , If you insert , Do not specify columns other than virtual columns , It will prompt the error , Because the value of the virtual column is missing ,

SQL> insert into customer3 values(1, '2022-01-01');
insert into customer3 values(1, '2022-01-01')
*
ERROR at line 1:
ORA-00947: not enough values

Can only be explicitly in INSERT Statement specifies columns other than virtual columns , Because the date field is of string type , Here, the date is in the format of string , No to_date(), But by definition , The virtual column will automatically calculate the value of this date field to_date() Type to store ,

INSERT INTO customer3(age, birthday) values(1, '2022-01-01');
INSERT INTO customer3(age, birthday) values(2, '2022-02-01');
INSERT INTO customer3(age, birthday) values(3, '2022-02-02');

Insert... At this time 、 After searching, you can see the corresponding date fields of string type and date type ,

SQL> SELECT * FROM customer3;
AGE BIRTHDAY PARTITON_KEY
---------- -------------------- ------------------
1 2022-01-01 01-JAN-22
2 2022-02-01 01-FEB-22
         3 2022-02-02           02-FEB-22

This puts forward higher requirements for development , In code SQL, It must be written in the form of clear and specific columns , If it is a system transformation , It's more invasive , therefore , It should be designed according to the standard development mode , To avoid these so-called workaround, But often , In some cases , We need to weigh these different schemes , But we can't just consider the convenience of development , Give all the questions to O & M , Simple maintenance will become complicated .

Recently updated articles ：

《MySQL The union index in 、 Covering index and leftmost matching principle 》

《 Binary and hexadecimal conversion operation 》

《 Recent problems 》

《 Rules for turning around on the road 》

《Oracle How to export index data blocks in ？》

Article classification and indexing ：

《 official account 900 Article classification and index 》