Add a column with default value as a constant in Postgres

Question

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

You have PARAGRAPH character varying(2000) and then 'Large text in here, around 4000 characters';, See the problem? — Adrian Klaver
– Adrian Klaver, Commented Feb 10, 2022 at 19:32
thanks @AdrianKlaver, not sure what could be the problem, I edited the character varying to be 4000, but really this was not the problem, even an empty string is failing. — JarochoEngineer
– JarochoEngineer, Commented Feb 10, 2022 at 19:55
what "errors" are you getting. Can you share the error message please. I don't think the problem is surfaced by your question yet. — Haleemur Ali
– Haleemur Ali, Commented Feb 10, 2022 at 19:57

Adrian Klaver · Accepted Answer · 2022-02-10 20:33:37Z

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

Collectives™ on Stack Overflow

Add a column with default value as a constant in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related