postgresql - To update several non dupliated rows at one time using "Group by"

Question

I followed the help from Generate a random number of non duplicated random number in [0, 1001] through a loop .

But I can't apply that to my case. When I do:

update weighed_directed_edge set endpoint= trunc(1000 * random()+ 1)
from  generate_series(1,10) group by 1 where startpoint= from_point;

to update endpointId it complains:

ERROR:  syntax error at or near "group"
LINE 1: ...nc(1000 * random()+ 1) from generate_series(1,10) group by 1.

I also tried:

insert into weighed_directed_edge (startpoint,endpoint)
values (from_point, trunc((1000 * random()+ 1) )
FROM generate_series(1, directed2number)
GROUP  BY 1 ;

insert into weighed_directed_edge (startpoint, endpoint, costs)
select 1, trunc(1000 * random()+ 1) from  generate_series(1,10) group by 1,1;

Not working. I need to select startpoint and endpoint from the same points set from another table, to populate weighed_directed_edge table.

• cross_point table : there're 1001 intersections.

                               Table "public.cross_point"
      Column    |  Type   |                                   Modifiers                              
  --------------+---------+---------------------------------------------------------------------
   intersection | integer | not null default nextval  ('intersection_intersection_seq'::regclass)
   x_axis       | integer | not null
   y_axis       | integer | not null
  Indexes:
      "intersection_pkey" PRIMARY KEY, btree (intersection)
      "intersection_x_axis_key" UNIQUE, btree (x_axis, y_axis)
  

• weighed_directed_edge table:

         Table "public.weighed_directed_edge"
         Column   |       Type       | Modifiers 
      ------------+------------------+-----------
       startpoint | integer          | 
       endpoint   | integer          | 
       costs      | double precision | 
      Indexes:
          "weighed_directed_edge_startpoint_key" UNIQUE, btree (startpoint, endpoint)
      Foreign-key constraints:
          "weighed_directed_edge_endpoint_fkey" FOREIGN KEY (endpoint) REFERENCES cross_point(intersection)
          "weighed_directed_edge_startpoint_fkey" FOREIGN KEY (startpoint) REFERENCES cross_point(intersection)
  

• A random number of endpoint (get from the 1001 points column randomly) corresponds to every startpoint (get from the points column sequentially),. The number of endpoint is in [1,7].

• Costs will be based on the distance between the startpoint and endpoint.

Requirements:

• The combination of startpoint and endpoint should be unique.

• Startpoints set contains all 1001 intersection from table cross_point

• Endpoints comes all from the same intersection point set.

• Every starpoint there're at most of 7 endpoint matches, (7 way road intersection is the maximum), and the number of endpoint matching is selected randomly.

I skipped the calculation of costs in the following code:

--The largest number of intersection chosen is 7, but this could be altered
create or replace function popluate_weighed_directed_edge() returns void as $$
declare
from_point integer;
to_point integer;
directed2number integer; --the number of node this startpoint leads to
counter integer;
factor float; 
weight numeric;
start_pointer record;

begin
for start_pointer in select * from cross_point
loop
from_point := start_pointer.intersection;
    directed2number := trunc(Random()*7+1);
    counter := directed2number;
        while counter > 0
        loop
insert into weighed_directed_edge (startpoint) select from_point from  generate_series(1,10) ;
update weighed_directed_edge set endpoint= trunc(1000 * random()+ 1) from  generate_series(1,10) group by 1 where startpoint= from_point ;
update weighed_directed_edge set costs= trunc(1000 * random()+ 1) from  generate_series(1,10) group by 1 where startpoint= from_point ;
        counter := counter - 1;
        end loop;
end loop;
end
$$ language plpgsql;

Answer 1

Within the loop you could draw a random number and only insert it (and decrement the loopcounter) if it does not exist. Pseudocode:

while (counter > 6) 
loop:
  this = 1+ random() *1000
  insert into weighed_directed_edge (startpoint, endpoint, costs)
  VALUES ( :frompoint, :this, xxx* random() )
  WHERE NOT EXISTS (
    SELECT(*) FROM weighed_directed_edge nx
    WHERE nx.startpoint = :frompoint
    AND nx.endpoint = :this
    );

  if (rowcount > 0) counter -= 1;
end loop;