[postgis-users] unexpected insert behavior

[Kirk R. Wythers]

Thanks Brent, I've been trying to bother you to much, see inline


On Apr 12, 2006, at 1:25 PM, Brent Wood wrote:

>
> Hi Kirk,
>
> --- "Kirk R. Wythers" <kwythers at umn.edu> wrote:
>>
>> To my mind this should insert a single row of tt_name = jack_pine and
>> cords = 21 into the cell with grid_id = 596524. However the below
>> SELECT statement shows that 16 rows were inserted.
>
>
> This insert will insert a single record into timber_volume for  
> EVERY record
> already in timber volume. As there are 16 null records, the result  
> is as
> expected. You can run the same select outside of the insert so you  
> can see what
> it actually returns. The next iteration will return & insert 32  
> records.....
>
> I suggest you use
>  INSERT INTO timber_volume (grid_id, tt_id, year, cords, tv_id) SELECT
>  596524, 5, 1920, 21, NEXTVAL('timber_volume_seq') LIMIT 1 FROM  
> timber_volume;
>
>
> The "LIMIT 1" should restrict it to just the one record, which I  
> think is what
> you want if you use this approach.
>
> There are other ways to achieve the same result, but this should  
> work OK.

I also "discovered" that the simple form of (without using SELECT):

INSERT INTO timber_volume (grid_id, tt_id, year, mbm, tv_id) VALUES  
(596680, 10, 1920, 2, NEXTVAL('timber_volume_seq'));

also worked. I'll your LIMIT 1 approach a try as well. Nice to  
understand why it was inserting all those records when I expected 1.

I've been playing around with views (trying to reduce the amount of  
typing), but now that I seem to be able to enter data with the  
command line, it is time to pick up PHP and figure out a more point  
and click approach for students to use.

Thanks again,

Kirk


>
>>
>> Can anyone see what I have done wrong that allowed caused 16 rows of
>> jack pine cords to be inserted when I was expected only 1 row?
>
>
> HTH...
>
>   Brent