From: | Darrell Fuhriman <darrell(at)garnix(dot)org> |
---|---|
To: | wes <pdxpug(at)the-wes(dot)com> |
Cc: | pdxpug(at)postgresql(dot)org |
Subject: | Re: Adding Missing Data to a Table |
Date: | 2011-03-29 22:06:07 |
Message-ID: | AF7FCC94-BE79-4F48-8B62-28D97182CEBB@garnix.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pdxpug |
>
> 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2011-03-29 22:32:37 | Re: Adding Missing Data to a Table |
Previous Message | Dan Colish | 2011-03-29 21:22:58 | Re: Adding Missing Data to a Table |