Re: Add columns to table; insert values based on row

On Thu, Nov 1, 2018 at 5:08 PM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

> On Thu, 1 Nov 2018, Rich Shepard wrote:
>
> > I'll use gawk to extract the relevant fields from the text file in which
> > they reside (in the correct order), then use emacs keyboard macros to add
> > the appropriate update text to each line. Must more efficient (and less
> > tedious) than manually writing each line.
>
> Actually a one-liner gawk script does the job.
>

I'm not sure what format your text file is in, but you could probably use
\copy to bring it into a (temporary) table in Postgres. As long as it had
your four new columns and the site_nbr, you could then update from that
table in one swoop:

UPDATE stations
SET start_date=new_data.start_date,
end_date=...
FROM new_data
WHERE stations.site_nbr=new_data.site_nbr;

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.