2

I am new to using Greenplum DB, was working on Oracle DB. I understand conceptually Greenplum in columnar DB and has different workings then Oracle.

The question I have is regarding altering table column length. I created a table with a column with say data type character varying(50). On top of this table I created a view which is summarizing some information. Later I wanted to change the length of column to character varying(100). It is not allowed in GP.

I had to drop view, change the column length and then re-create the view. I do not believe that this was the case on Oracle. Not sure why GP has to to this.

Does someone has understanding as to how to avoid this?

Marco
  • 3,720
  • 5
  • 25
  • 31
user5319411
  • 21
  • 2

2 Answers2

1

It's not even about the type or subtype. Changing a column on a table requires you to drop and recreate all dependencies that reference it. You can do this in a single transaction.

CREATE TABLE foo ( a varchar(10) );
CREATE VIEW bar AS TABLE foo;

BEGIN;
  DROP VIEW bar;
  ALTER TABLE foo
    ALTER COLUMN a
    SET DATA TYPE varchar(100);
  CREATE VIEW bar AS TABLE foo;
COMMIT;
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
0

Whenever you alter or drop/create a table in Greenplum, the associated ID will change in metadata tables. Hence a view referring to old ID won't work. That's the reason why the view need to be recreated.

Steps:

  1. ALTER TABLE
  2. DROP VIEW
  3. RECREATE VIEW
András Váczi
  • 31,778
  • 13
  • 102
  • 151
Shivaraj
  • 11
  • 3