2

Trying to upgrade PostgreSQL from 11.20 to 12.15 and hitting the below error

pg_restore: error: could not execute query: ERROR: type "abstime" does not exist

I do understand that Data types abstime, reltime, and tinterval were removed by postgres under enhancements or version upgrades. but I believe there must be someone in the community who must have faced this issue and managed to find a solution to this problem.

is there anyone in community could help as would like to make progress and keep things secure.

tried to look into KB articles but couldn't find any solution. Looking for solution

Improve this question

1 Answer 1

Reset to default
3

Replace all occurrences of abstime with timestamp(0) with time zone. You'll have to do that in the v11 database before you upgrade, unless you want to manually edit a dump file.

abstime had been deprecated for almost 20 years when it was removed, and it had a Y2038 problem.

Here is a command to convert a column as described above:

ALTER TABLE table_name
   ALTER column_name TYPE timestamp(0) with time zone;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I know this was a while ago but. running into this issue now, and wondering if you could suggest a command to do this? (I'm not very familiar with pg / sql but have been assigned the task of upgrading a database from 9.6 to 16 haha)
@glass-ships I have never heard of version "16 haha", but I have added an ALTER TABLE statement to the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.