phpPgAdmin automatically creates null row during adding a column

Question

I have a table, called "Route" (part of a production postgres database). Because of a missing column "arrival_time", I used the "Add column" function of phpPgAdmin. There, I added each important property (name, type). When I browse the column after it was created, there is automatically created a value:

arrival_time | count
--------------------
NULL         | 9358

Because of this, I cannot set the constraint "NOT_NULL", but which is required. What is the reason for this automatically created value? And how can I avoid it or fix this issue?

score 2 · Accepted Answer · answered Jan 21 '15 at 12:54

This is not a phpPgAdmin issue. You must either add the new column with NULL (which is the default anyway), or add with NOT NULL constraint and a DEFAULT clause. If neither is present, PostgreSQL doesn't know what to do with existing rows.

When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.

If you already added the column, you can use a single UPDATE to set all existing rows to a starting value, f.ex.:

UPDATE table SET arrival_time = NOW();

After that, you can add a NOT NULL constraint to this column.

Thank you. I removed the column and then I added it again with NOW as default and not_null as constraint. After the creation I removed the 'now' constraint. Thx :) — dildik, Jan 21 '15 at 13:54

phpPgAdmin automatically creates null row during adding a column

1 Answers1