Pivotal Knowledge Base

Follow

Pivotal Greenplum (GPDB) restore reports column has type "unknown", Proceeding with relation creation anyway

Problem

During a restore operation, or in general when a table is created, you can see warning messages indicating that a column has unknown datatype. Below is an example for 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)

Solution

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 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)

Comments

Powered by Zendesk