Set the value of a column to its default value

Question

I have few existing tables in which I have to modify various columns to have a default value.

How can I apply the default value to old records which are NULL, so that the old records will be consistent with the new ones

ALTER TABLE "mytable" ALTER COLUMN "my_column" SET DEFAULT NOW();

After modifying table looks something like this ...

    Table "public.mytable"
 Column      |            Type             |                        Modifiers
-------------+-----------------------------+-----------------------------------------------
 id          | integer                     | not null default nextval('mytable_id_seq'::regclass)
 ....

 my_column   | timestamp(0) with time zone | default now()

Indexes:
  "mytable_pkey" PRIMARY KEY, btree (id)

Is there a simple to way to have all columns which are currently null and also which have a default value to be set to the default value ?

Answer 1

Deriving from insert into:

For clarity, you can also request default values explicitly, for individual columns or for the entire row:

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

I just tried this, and it is as simple as

update mytable
set my_column = default
where my_column is null

See sqlfiddle