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

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

> I have the following code in a script:
>
> alter table stations add column start_date date;
> alter table stations add column end_date date;
> alter table stations add column howmany integer;
> alter table stations add column bin_col char(8);
>
> insert into stations (start_date, end_date, howmany, bin_col) values ( )
> select site_nbr from stations
> where site_nbr = ' ';
>
> The table has 82 rows. Is there a more elegant way to insert data
> specific
> to a site_nbr other than 82 repetitions of the insert statement? (I suspect
> not, but I might be wrong and learn something valuable by asking.)
>
>
That makes no sense to me...you already have 82 rows on the table so if you
insert 82 more you'll have 164 which doesn't seem like what you would
want...

I would probably do:

CREATE TABLE stations_ext (site_nbr, start_date date, ...)

COPY stations_ext FROM filename;

UPDATE stations SET start_date = stations_ext.start_date, ...
FROM stations_ext
WHERE stations.site_nbr = stations_ext.site_nbr;

OR

UPDATE stations SET start_date = 'literal date'::date WHERE site_nbr =
'literal site number';
... 82 more times as appropriate
But I would build out those UPDATE statements in a spreadsheet
Either way I'd get the relevant new data into tabular format with a
site_nbr associated first.

David J.