0

Database that I am using is postgresql. What I want to do is :-

  1. I have an array which stored different string values (e.g. size of 4 [a,b,c,d]
  2. I have a table column called "secret"
  3. I want to insert all the values from the array into the table for every single row (e.g. Let us say we have 4 rows in the table, row 1, row 2, row 3 and row 4. Value "a" from the array should be inserted into row 1, while value "b" should be inserted into row 2, same goes to the others. Basically just update all the rows.

Here is my code :-

secret:= make([]string, count) //count = all the row from the column.
for i := range secret {
    _ = i
    testSecret:= random_string.GenerateString(10) //method for generating random string

    secret = append(secret, testSecret)

}
//update query
pgUpdate := `UPDATE table SET secret= $1`
err = db.QueryRow(pgUpdate, secret).Scan()
if err != nil {
    log.Fatalf("Unable to execute the UPDATE query. %v", err)
}

Error that I got "type: unsupported type []string, a slice of string"

I am new to golang, not sure what I should look for so if anything else please educate me.

AeF
  • 1
  • 1
  • Does the following answer your question? https://stackoverflow.com/a/62889212/965900 – mkopriva Nov 12 '21 at 08:24
  • @mkopriva thank you for your answer, will try it out. – AeF Nov 12 '21 at 14:41
  • @mkopriva I only want to update one column, but all the rows with unique string. I don't have any id column to support the SQL statement, is it still possible to be done? I want to do something like update the rows one by one while generating the unique string. – AeF Nov 13 '21 at 09:43
  • How do you know which rows to update if you can't identify them by some unique value? Or do you want to update every single row in the table? – mkopriva Nov 13 '21 at 09:46
  • Every single row yes. But every row with unique strings. – AeF Nov 13 '21 at 09:49
  • Does your table have only 4 rows then? I'm asking because of the `make([]string, 4)` (to which you then *append*, which is wrong, but that's a separate issue). – mkopriva Nov 13 '21 at 09:49
  • Honestly, it doesn't have only 4 rows. I have done that with 'SELECT COUNT(*) FROM table' then I use the count as the size of it. – AeF Nov 13 '21 at 09:54
  • Sorry for the inconvenience. – AeF Nov 13 '21 at 09:55

1 Answers1

0

I wrote a simple query using dynamic SQL. Example:

do 
$body$
declare
    per RECORD;
begin 

    FOR per IN
        select 'insert into table1 (' || string_agg(main.fnames, ',') || ') values (' || string_agg(main.sval::text, ',') || ');' as p_sql from (
            select 
                'row' || (ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))::text AS fnames,
                '''' || t2.sval || '''' as sval 
            from (  
                select t1.sval from unnest('{a,b,c}'::text[]) as t1(sval)
            ) t2
        ) main 
    LOOP    
        execute per.p_sql;
    end loop;
   
end; 
$body$
LANGUAGE 'plpgsql';
Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8