Skip site navigation (1) Skip section navigation (2)

Re: Adding Missing Data to a Table

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 (view raw or flat)
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;

In response to

Responses

pdxpug by date

Next:From: Rich ShepardDate: 2011-03-29 22:32:37
Subject: Re: Adding Missing Data to a Table
Previous:From: Dan ColishDate: 2011-03-29 21:22:58
Subject: Re: Adding Missing Data to a Table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group