tencent cloud

$0 14-Day TrialExperience EdgeOne for acceleration and security protection!

Feedback

TencentDB for PostgreSQL

Structure Compatibility Check

Last updated: 2024-11-04 10:16:55

Check Details

If the target database version is 12 or later and the table to be migrated contains three data types: abstime, reltime, and tinterval, the verification task will report an error.
The data types abstime, reltime, and tinterval are old time and date types that have been deprecated in the latest PostgreSQL version. It is recommended to replace them with the following types:
abstime: It can be replaced with the timestamp or timestamp with time zone type. These types provide a broader range of capabilities for representing dates and times.
reltime: It can be replaced with the interval type. The interval type is used to represent time intervals and can include units such as years, months, days, hours, minutes, and seconds.
tinterval: It can be replaced with the tsrange or tstzrange type. These types are used to represent time ranges and can include start and end times.

Fixing Method

The modification method for the three data types abstime, reltime, and tinterval is similar. First, create a column, convert the old data format to the new data format, and store it in the new column. After verifying the new column data is correct, delete the old column. Finally, if needed, rename the new column to the name of the old column.
The following example shows how to modify abstime to timestamp.
1. In the table where the verification task reports an error, add a new column of the timestamp type.
ALTER TABLE your_table ADD COLUMN new_column TIMESTAMP;
2. Convert the data in the abstime column to the timestamp type and store the result in the new column.
UPDATE your_table SET new_column = your_abstime_column::TIMESTAMP;
3. Verify whether the data in the new column is correct.
SELECT * FROM your_table;
4. If the data in the new column is correct, you can delete the old abstime column.
ALTER TABLE your_table DROP COLUMN your_abstime_column;
5. If needed, you can rename the new column to the old column's name.
ALTER TABLE your_table RENAME COLUMN new_column TO your_abstime_column;

Catalog

In The Article

Structure Compatibility Check

Contact Us

Contact our sales team or business advisors to help your business.

Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

7x24 Phone Support
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon