[postgis-users] unexpected insert behavior

[Brent Wood]

Hi Kirk,

--- "Kirk R. Wythers" <kwythers at umn.edu> wrote:

> Hi all,
> 
> Sorry for the novel length question, but I need a hand figuring out  
> what I missed in database design and the only way I can think to ask  
> the question is to provide some background. Here goes...
> 

> INSERT INTO timber_volume (grid_id, tt_id, year, cords, tv_id) SELECT  
> 596524, 5, 1920, 21, NEXTVAL('timber_volume_seq') FROM timber_volume;
> 
> 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.

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