MySQL type YEAR is not supported

[ppolydoras]

I'm importing a MySQL schema with a table that includes a YEAR datatype column.

CREATE TABLE `test_dts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `year` year DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This fails to be imported with the error message:
type "year" does not exist

[surajkharage19]

Hi @ppolydoras,

Thanks for reporting this issue.

I can reproduce this issue at my end. As error message suggests, Postgres does not have YEAR data type (or equivalent).

As per MySQL documentation, Year is a 1-byte type used to represent year values. I think, we can map MySQL year to Postgres's smallint data type but there might be some behavior differences if we do so.

for e.g:

MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, and 0000.
As 1- or 2-digit strings in the range '0' to '99'. MySQL converts values in the ranges '0' to '69' and '70' to '99' to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.

As 1- or 2-digit numbers in the range 0 to 99. MySQL converts values in the ranges 1 to 69 and 70 to 99 to YEAR values in the ranges 2001 to 2069 and 1970 to 1999.

We could not be able to achieve the above behaviour if we map year to smallint in Postgres. I don't think we have a better workaround for the same apart from above. Do you have any suggestions on this?

[ppolydoras]

Generally speaking, SMALLINT is the best solution. Perhaps in certain use-cases DATE would be more preferable, since they are both time-related datatypes, but it's certainly not the most generic choice.

[surajkharage19]

Thanks.

We will take this further and try to fix mapping MySQL's year type to Postgres smallint.

[surajkharage19]

Hi @ppolydoras,

After careful analysis, we decided we are not going to fix this (MySQL's year map to smallint). YEAR as a data type has certain constraints and limitations and we can't achieve those completely in Postgres.

You can manually import those tables which have YEAR data type and map those to smallint/numeric as per your need.