Question 114683 in PostgreSQL Question posted in
The official documentation can be found here.

How to make select every iteration Postgresql

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);
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search