Column removed but not removed, how to remove a column?

Question

PostgreSQL

Hello everyone, I created a table, I add a column to it. Then I realized that I created it incorrectly, I wanted to apply a modifier, but I got an error: well, I take and delete the column. I create it again, and there: ERROR: column "columnname" contains NULL values ​​Well, of course, I look in \d tablename, and my column is not there.
alter table tablename add column columnname text;
столбец "columnname" содержит значения NULL
alter table tablename drop columnname;

alter table tablename drop column if exists columnname;

ЗАМЕЧАНИЕ:  столбец "columnname" в таблице"tablename" не существует, пропускается
ALTER TABLE

alter table tablename add column columnname text not null;

ОШИБКА:  столбец "columnname" содержит значения NULL

And how to deal with it?

Answer 1

My crystal ball showed exactly right.
When you do add column columnname text - you get NULL in that entire column. Of course, right after that, you cannot put NOT NULL in any way. Any string violates this restriction and the base refuses to put NOT NULL.
When you do add column columnname text NOT NULL - well, that might work in one case - you have an empty table. Then no row violates the NOT NULL constraint, you just have 0 rows. If there is at least one line, it is impossible. You are asking for default NULL and NOT NULL at the same time - that doesn't happen. The error message refers specifically to the column being added at that moment, not to some other previously existing one.
You must specify some non-NULL default if you want to put NOT NULL on the generated field. Or clear the table.
Or create without NOT NULL, fill in the field, then do NOT NULL.
In postgresql, a column is never actually deleted. But this is an implementation detail and has nothing to do with the essence of your question.