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

   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.)

Regards,

Rich

On 11/1/18 1:23 PM, Rich Shepard 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 = ' ';

I am not following the above.

Are you trying to add new data to existing records?

If so where is the new data coming from?

> 
>    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.)
> 
> Regards,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 1 Nov 2018, Adrian Klaver wrote:

>> 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 = ' ';

> Are you trying to add new data to existing records?

Adrian,

   I am adding four columns to an existing table that already contains four
columns.

> If so where is the new data coming from?

   I have a text file and will fill each insert statement by hand if there's
not a more efficient way to do this.

Regards,

Rich

On Thu, 1 Nov 2018, David G. Johnston wrote:

> 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:

David,

   The table already exists with four attribute columns and their data. I'm
adding four new columns and their data base on the existing row's value in
column site_nbr.

   Based on your and Adrian's responses I'll go ahead and enter the values
filled intert statements for each row.

Thanks,

Rich

On 11/1/18 1:53 PM, Rich Shepard wrote:
> On Thu, 1 Nov 2018, David G. Johnston wrote:
> 
>> 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:
> 
> David,
> 
>    The table already exists with four attribute columns and their data. I'm
> adding four new columns and their data base on the existing row's value in
> column site_nbr.
> 
>    Based on your and Adrian's responses I'll go ahead and enter the values
> filled intert statements for each row.

Why? As David said that will get you an additional 82 rows with data 
that is dissociated from the existing data. You want to update the 
existing rows with the new field data for each station.

> 
> Thanks,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 1 Nov 2018, Adrian Klaver wrote:

> Why? As David said that will get you an additional 82 rows with data that is 
> dissociated from the existing data. You want to update the existing rows with 
> the new field data for each station.

   D'oh! Of course!

Thanks both,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:

> On Thu, 1 Nov 2018, Adrian Klaver wrote:
>
>>> 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 = ' ';
>
>> Are you trying to add new data to existing records?
>
> Adrian,
>
>    I am adding four columns to an existing table that already contains four
> columns.
>
>> If so where is the new data coming from?
>
>    I have a text file and will fill each insert statement by hand if there's
> not a more efficient way to do this.
>
> Regards,
>
> Rich

Like others, I'm not clear on exactly what your after here, but did want
to point out

1. If your doing it by hand, you don't have to do a separate 'full'
insert statement for every row i.e.

insert into blah (x, y, z) values
(....),
(....),
(....),
....
(...);

is valid syntax. You don't need to do a full "insert into blah (....)
values (...)" for each insert.

2. If it really is an insert you want to do and you already have the
data in a file e.g. CSV or similar, then you can use the \copy command
to process the file, which is very fast.

3. Is it really insert or update you need?


-- 
Tim Cross

On Fri, 2 Nov 2018, Tim Cross wrote:

> 3. Is it really insert or update you need?

Tim,

   Update.

   It's been a long time since I did any database manipulation (I just use
the existing databases as each project needs its data) so I sometimes make
these types of mistakes.

   Your idea of manipulating a text file is a great one!

   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.

Thanks,

Rich

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.

   Thanks again for the suggestion!

Regards,

Rich

On Thu, 1 Nov 2018, Ken Tanzer wrote:

> 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:

Ken,

   Thank you.

Rich