Re: Adding Missing Data to a Table

>
> there are many options. One such is to find a column (or combination of columns) that has a unique value and use updates from a shell script.

you can also do it in a function. Something like this (replacing my_table and some_id with the respective table and column name):

CREATE OR REPLACE FUNCTION fill_in_the_blanks() RETURNS SETOF my_table
AS $$
DECLARE
num_empty_rows integer;
max_id integer;
j integer;
my_row my_table%rowtype;

BEGIN
num_empty_rows := count(*) from my_table where some_id is NULL;
max_id := max(some_id) from my_table;
IF max_id IS NULL THEN
max_id := 1;
END IF;
j:=1;
FOR my_row IN SELECT * from my_table WHERE some_id IS NULL LOOP
my_row.some_id := max_id + j;
j:=j+1;
RETURN NEXT my_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;

BEGIN;

select * into temp table foo from fill_in_the_blanks();
delete from my_table where some_id IS NULL;
insert into my_table select * from foo;
create sequence my_table_some_id_seq start with 1 increment by 1 cache 1;
select setval('my_table_some_id_seq'::regclass, max(some_id)+1) FROM my_table;
alter sequence my_table_some_id_seq owned by mytable.some_id;
alter table my_table alter some_id set not null;
alter table my_table alter some_id set default nextval('my_table_some_id_seq'::regclass);
drop table foo;
drop function fill_in_the_blanks();
COMMIT;