CODEWITHSUNDEEP
data-warehousenetezza
iman453
iman453

Reputation: 9535

Change NOT NULL constraint to NULL in Netezza

Is there any way I can change a non nullable column to nullable in Netezza? (Or will I have to create a new nullable column in the table, transfer the data over, and delete the old column?)

Sorry if the answer shows up with a google search, I've tried looking and haven't been able to find something.

Upvotes: 1

Views: 10345

Answers (1)

N West
N West

Reputation: 6819

There's not much you can do to alter columns in NZ 6.0, aside from add/drop the column itself, setting or clearing the default value, changing the length of a varchar, and renaming a column.

You'll probably have to create a new column, move the data, drop the old column. For a small table, this should be OK.

ALTER TABLE t RENAME COLUMN c to c_old; 

ALTER TABLE t ADD COLUMN (c bigint);

UPDATE t set c = c_old;

ALTER TABLE t DROP COLUMN c_old CASCADE;

However, if the table is large:

Because of how netezza executes update statements, it may just be better to create a whole new table and move the data over. NZ requires a groom of the original table afterwards to delete the old version of the updated records.

CREATE TABLE t_new ( c bigint ) DISTRIBUTE ON (c);

INSERT INTO t_new SELECT c FROM t;

ALTER TABLE t RENAME to t_old;

ALTER TABLE t_new RENAME to t;

DROP TABLE t_old;

Upvotes: 4

Related Questions