I have table names with name and surname columns. I want to grab random name and surname from it, i tried this, but it takes one name and surname and prints it 100 times so it makes only one select at the start and then uses it's value,how can i fix it?
SELECT (SELECT name FROM names WHERE ID = ROUND(RANDOM() * 10 + 1)),
(SELECT surname FROM names WHERE ID = ROUND(RANDOM() * 10 + 1))
FROM GENERATE_SERIES(1, 100);
In order for Postgres to evaluate the select in the subquery multiple times, it needs to look like a correlated subquery -- one whose results depend on the values being returned by the top-level query. A minor problem here is that you don't actually care about those values. You can hack around that by meaninglessly including them in the subqueries, like this:
SELECT (SELECT name FROM names WHERE ID = ROUND(RANDOM() * 10 + 1 + i - i)),
(SELECT surname FROM names WHERE ID = ROUND(RANDOM() * 10 + 1 + i - i))
FROM GENERATE_SERIES(1, 100) i;
Another approach would be to move the subqueries to your FROM clause, put a different generate_series clause in each one, and then join them on the output of each series, but that ends up being really complicated SQL.
you didn't use the generateseries in the subquery , try with this
SELECT (SELECT name FROM names WHERE ID = ROUND(RANDOM() * 10 + g)),
(SELECT surname FROM names WHERE ID = ROUND(RANDOM() * 10 + g))
FROM GENERATE_SERIES(1, 100) g;
