Randomly insert 1 of 3 declared variables

Question

I have three variables that are declared and have an integer value assigned to them.

I am trying to randomly assign the integer value to a field in an UPDATE statement, but get an error.

This is statement I am trying to execute:

FOR user_record IN (SELECT * FROM users_to_add) LOOP
    UPDATE 
        customer."user"
    SET
        primary_site_id  = ({site_GRO, site_WHS, site_SHR}[])[ceil(random()*3)],
    WHERE
        userid = (SELECT userID FROM customer.user
                  WHERE emailaddress=user_record.email_address);           
END LOOP;

I am getting:

SyntaxError: syntax error at or near "{"

This same format works if the value being randomly selected is a string but since these are variables, the inside curly brackets can't be enclosed in quotes.

Please always provide your version of Postgres and a complete function / `DO` statement to make this easier. Are `site_GRO`, `site_WHS`, and `site_SHR` supposed to be separate variables or come from `customer."user"`? — Erwin Brandstetter, Sep 25 '19 at 22:15

Erwin Brandstetter · Answer 1 · 2019-09-25T22:24:22.170

Use an ARRAY constructor instead of the (invalid) array literal.

(ARRAY[site_GRO, site_WHS, site_SHR])[ceil(random()*3)]

However, a set-based solution is typically more efficient than looping:

UPDATE customer."user" u
SET    primary_site_id  = CASE trunc(random()*3)::int
                             WHEN 0 THEN site_gro  -- your variables here
                             WHEN 1 THEN site_whs
                             WHEN 2 THEN site_shr
                           END
FROM   users_to_add ua
WHERE  u.userid = ua.email_address;

Should achieve the same. Works inside a PL/pgSQL block or as standalone SQL DML command (then you need to interpolate variable values yourself).

A single multi-row UPDATE is much cheaper than many updates in a loop.

trunc() is slightly more correct than ceil(), as random() returns a value in the domain [0,1) (1 excluded). It's also faster.

And a CASE construct is substantially faster than building an array just to extract a single element from it.

Asides:

Avoid reserved words like user as identifiers. Always requires double-quoting, and can lead to confusing errors when forgotten. Also avoid random capitalization in identifiers. This goes for SQL as well as for PL/pgSQL. See:

Are PostgreSQL column names case-sensitive?

score 0 · Answer 2 · answered Sep 25 '19 at 22:13

Perhaps you can try splitting the index and array out into their own vars?

FOR user_record IN (SELECT * FROM users_to_add) LOOP
  a := ARRAY[site_GRO, site_WHS, site_SHR];
  i := ceil(random()*3);
  UPDATE 
    customer."user"
  SET
    primary_site_id  = a[i]
  WHERE
    userid = (SELECT userID FROM customer.user WHERE emailaddress=user_record.email_address);        
END LOOP;

Randomly insert 1 of 3 declared variables

2 Answers2