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

Re: Adding Missing Data to a Table

From: wes <pdxpug(at)the-wes(dot)com>
To: pdxpug(at)postgresql(dot)org
Subject: Re: Adding Missing Data to a Table
Date: 2011-03-29 21:16:53
Message-ID: AANLkTikj7AzUG1MnRdaFBig361fGSz1O5-hSZQ=UpX8f@mail.gmail.com (view raw or flat)
Thread:
Lists: pdxpug
On Tue, Mar 29, 2011 at 1:23 PM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>wrote:

> On Tue, 29 Mar 2011, wes wrote:
>
>  I don't know the most efficient way to automate such a task, I only know
>> my
>> way, which would be to make it up as I go along.
>>
>
> Wes,
>
>  And this requires knowing the potential options.


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.

'select unique_column[,some other column if needed] from table where record
IS NULL;' > rows-to-update
seq 30000 "$(wc -l rows-to-update)" > new-record-values

then use the various textual gymnastics involved in constructing update
queries and combining the two files to end up with lines like:

update table set record=30000 where unique_column=value_a [AND
unique_column_2=value_2a];
update table set record=30001 where unique_column=value_b [AND
unique_column_2=value_2b];

and so on.

I'm sure there are much better answers, but since all I have is a hammer,
your problem looks suspiciously like a nail to me :)

-wes

In response to

Responses

pdxpug by date

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

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