postgres altering table column is skipped-CodePudding

I altered a table using the command

ALTER TABLE sys.system_property
ADD COLUMN IF NOT EXISTS description text;
COMMENT ON COLUMN sys.system_property.description IS 'description of property';

I then need to have a default value and set it no Not NULL so I did:

ALTER TABLE sys.system_property
ADD COLUMN IF NOT EXISTS description text NOT NULL DEFAULT 'Missing Description';
COMMENT ON COLUMN sys.system_property.description IS 'description of property';

I added the line NOT NULL DEFAULT 'Missing Description'; to make the column Not Null and have a DEFAULT VALUE but running this code didnt alter the table the code is skipped.

ALTER TABLE sys.system_property
ADD COLUMN IF NOT EXISTS description text NOT NULL DEFAULT 'Missing Description'
[2022-11-04 12:02:22] [42701] column "description" of relation "system_property" already exists, skipping

How to alter column ?

CodePudding user response：

The column already exists, so ADD is not appropriate. Use:

alter table alter column set not null default 'Missing Description';`

See documentation.

CodePudding user response：

ALTER TABLE sys.system_property 
   ALTER COLUMN price SET NOT NULL DEFAULT 'Missing Description';