Home > Mobile >  How to make select every iteration Postgresql
How to make select every iteration Postgresql

Time:09-13

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;

  • Related