Question & Answer
Question
How do I add a column to an existing table?
Answer
Netezza does not have a variant of ALTER TABLE ADD COLUMN () so it may seem that there is no way to add a column to an existing table. (ADD COLUMN is not currently supported functionality on the NPS system.)
However, the following workaround allows you to add a new column to an existing table. It uses CTAS (Create Table AS) to create a new modified table and then copies the data into the new table.
This is a three step process:
- Create an empty table with the new column.
- Set the privileges on the new table to match the existing table.
- Insert into the new table from the old one.
The following is an example of the above (adding a new column as CHAR 20 ):
- nzsql=> CREATE TABLE tablename_new AS SELECT *, CAST('' AS CHAR(20)) AS new_column FROM tablename LIMIT 0 DISTRIBUTE ON RANDOM;
nzsql=> ALTER TABLE tablename_new SET PRIVILEGES TO tablename;
nzsql=> INSERT INTO tablename_new SELECT * FROM tablename;
Once there is a new table with the added column, you can rename tables so that the modified table is the one you use:
- nzsql=> ALTER TABLE tablename RENAME TO tablename_old;
nzsql=> ALTER TABLE tablename_new RENAME TO tablename;
As usual, you must account for your data types in the new column(s) and make sure that you define the distribution for the new table.
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ390987
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21574087