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);
CodePudding user response:
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.
CodePudding user response:
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;