Is there a nesting limit for subqueries in PostgreSQL?

Question

I am constructing the query string in code and the easiest way I see to add new parameters is to add subqueries something like this:

query = "SELECT * FROM table";
query = "SELECT * FROM (" + query 
         + MessageFormat.format(" ) as subquery where x = {0}", y);
query += ";";

So I might have quite a lot of such subqueries at the end and I wonder if there is a limit I might hit.

I suspect not; views are essentially just sub-queries so the nested depth could be much deeper than appears. It should be quite easy to test though. Just recurse a trivial example 1000 times deeper than you ever expect to go... — MatBailie
– MatBailie, Commented Sep 8, 2016 at 15:11
In theory: barely. In practice: it depends. BTW: your example is too trivial, the optimiser probably will unfold it. :: select * FROM( select * FROM(select * from t) a ) b; can be reduced to a plain select * from t; — wildplasser
– wildplasser, Commented Sep 8, 2016 at 21:28

anil · Accepted Answer · 2016-09-08 15:13:09Z

1

Not really - You should go ahead and try it. However, I believe there could be a performance impact though. To the extent possible use joins and such direct methods.

answered Sep 8, 2016 at 15:13

anil

6681 gold badge8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Webmut · Accepted Answer · 2016-09-08 16:49:58Z

0

I use nested subqueries extensively myself, even for a similar use-case, and in PostgreSQL I haven't reached any kind of limit. For example in MySQL the situation is different, as you can only reference fields in a first-level subquery; any deeper and you're out of luck.

answered Sep 8, 2016 at 16:49

Webmut

2,9582 gold badges20 silver badges14 bronze badges

Collectives™ on Stack Overflow

Is there a nesting limit for subqueries in PostgreSQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related