Add a column with default value as a constant in Postgres-CodePudding

I'd like to alter an existing table to add a column which defines a large string as default in Postgres DB.

I have tried the following:

DO $$
declare PARAGRAPH character varying(4000):= 'Large text in here, around 4000 characters';
begin
    ALTER TABLE USERS
    ADD COLUMN NOTES_TEXT character varying(4000) DEFAULT PARAGRAPH NOT NULL;
END $$;

Another way I found is the following:

DO $$
declare PARAGRAPH character varying(4000);
begin
    select 'Very large text goes in here.' into PARAGRAPH;
    
    ALTER TABLE USERS
    ADD COLUMN NOTES_TEXT character varying(4000) DEFAULT PARAGRAPH NOT NULL;
END $$;

However, I am getting errors in both attempts related to the variable not recognized.

Do you know if this is possible in Postgres?

Thanks a lot

CodePudding user response：

The issue was this:

DO $$
declare PARAGRAPH character varying(4000);
begin
    PARAGRAPH := 'Very large text goes in here.';
    
    ALTER TABLE USERS
    ADD COLUMN NOTES_TEXT character varying(4000) DEFAULT PARAGRAPH NOT NULL;
END $$;
ERROR:  cannot use column reference in DEFAULT expression
CONTEXT:  SQL statement "ALTER TABLE USERS
    ADD COLUMN NOTES_TEXT character varying(4000) DEFAULT PARAGRAPH NOT NULL"
PL/pgSQL function inline_code_block line 6 at SQL statement

The solution from here Dynamic SQL:

DO $$                                     
declare PARAGRAPH character varying(4000);
begin
    PARAGRAPH := 'Very large text goes in here.';
    
    EXECUTE 'ALTER TABLE USERS ' ||
    'ADD COLUMN NOTES_TEXT character varying(4000) DEFAULT' ||quote_literal(PARAGRAPH) || 'NOT NULL';
END $$;

\d users
...
 notes_text      | character varying(4000) |           | not null | 'Very large text goes in here.'::character varying