0

I am trying to port a database where there is a table with a column of type unknown. pg_restore raised the error

pg_restore: [archiver (db)] could not execute query: ERROR: column "color" has pseudo-type unknown`.

I don't understand why such tables can exist given that the doc says

A pseudo-type cannot be used as a column data type

However, I want to keep the database as it is and do not do any modifications. Which error code should I specify to catch this exception?

1

This must be a dump from an old PostgreSQL database where it was still possible to generate unknown columns.

The best thing would be to fix the source database, but if you cannot do that, generate an SQL script from the dump with pg_restore and fix the column definitions there.

  • The old server is running on postgres 9.0, and the new one is running on version 10.8. However, I did not find unknown listed in pseudo-types in the doc of version 9.0. – kumom Jul 2 at 10:31
  • In addition, the doc of 9.0 also says "A pseudo-type cannot be used as a column data type" – kumom Jul 2 at 10:32
  • unknown became a pseudo-type with commit d8d32d9a5. Creation of tables with unknown columns was disabled in commit 1e7c4bb004. Both commits were in January 2017, long after 9.0. – Laurenz Albe Jul 2 at 11:11
  • I see, thanks a lot! – kumom Jul 5 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.