Pivotal Greenplum Restore Reports "column has type "unknown", Proceeding with relation creation anyway"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

During a restore operation, or in general when a table is created, you can see warning messages indicating that a column has an unknown datatype. Below is an example of the warning message seen.

gpadmin=# create table dummy as select '1' as a, 'd'::text as d distributed by (d);
WARNING:  column "a" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

gpadmin=# \d dummy
     Table "public.test1"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | unknown |
 d      | text    |
Distributed by: (d)

Environment

Resolution

In Pivotal Greenplum, which is based out of postgres, unknown column type is allowed. However, you will not be able to insert values other than character / varchar datatype. So, be careful while creating views or tables and define proper data type for a column. However, if in case some user has created tables / view with unknown data type, one can alter the column data type later as required to rectify it.

The warning message does not indicate a failure of gpdbrestore operation.

Below is an example to alter unknown datatype to an integer:

gpadmin=# alter table dummy alter a type int ;
ALTER TABLE
gpadmin=# \d dummy
          Table "public.dummy"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 a      | integer           |
 d      | text              |
Distributed by: (d)