Selecting ambiguous column from subquery with postgres join inside

Question

I have the following query:

select x.id0 
from (
  select * 
  from sessions 
     inner join clicked_products on sessions.id0 = clicked_products.session_id0 
) x;

Since id0 is in both sessions and clicked_products, I get the expected error:

column reference "id0" is ambiguous

However, to fix this problem in the past I simply needed to specify a table. In this situation, I tried:

select sessions.id0 
from (
   select * 
   from sessions 
     inner join clicked_products on sessions.id0 = clicked_products.session_id0 
) x;

However, this results in the following error:

missing FROM-clause entry for table "sessions"

How do I return just the id0 column from the above query?

Note: I realize I can trivially solve the problem by getting rid of the subquery all together:

select sessions.id0 
from sessions 
  inner join clicked_products on sessions.id0 = clicked_products.session_id0;

However, I need to do further aggregations and so do need to keep the subquery syntax.

you can have a alias for the column inside subquery and use it outside — krithikaGopalakrishnan
– krithikaGopalakrishnan, Commented Sep 26, 2018 at 6:19
Don't use select * in the derived table. Select the columns you need — user330315
– user330315, Commented Sep 26, 2018 at 6:22
Because you use *, specify your column because in your result sub query you have 2 id0 and that make ambiguous. — dwir182
– dwir182, Commented Sep 26, 2018 at 6:23

Laurenz Albe · Accepted Answer · 2018-09-26 06:21:29Z

1

The only way you can do that is by using aliases for the columns returned from the subquery so that the names are no longer ambiguous.

Qualifying the column with the table name does not work, because sessions is not visible at that point (only x is).

True, this way you cannot use SELECT *, but you shouldn't do that anyway. For a reason why, your query is a wonderful example:
Imagine that you have a query like yours that works, and then somebody adds a new column with the same name as a column in the other table. Then your query suddenly and mysteriously breaks.

Avoid SELECT *. It is ok for ad-hoc queries, but not in code.

answered Sep 26, 2018 at 6:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

krithikaGopalakrishnan · Accepted Answer · 2018-09-26 06:23:04Z

0

select x.id from
 (select sessions.id0 as id, clicked_products.* from sessions
 inner join
 clicked_products on
 sessions.id0 = clicked_products.session_id0 ) x;

However, you have to specify other columns from the table sessions since you cannot use SELECT *

answered Sep 26, 2018 at 6:23

krithikaGopalakrishnan

1,33513 silver badges33 bronze badges

Comments

Grzegorz Gierlik · Accepted Answer · 2018-09-26 06:27:01Z

0

I assume:

select x.id from (select sessions.id0 id
  from sessions 
  inner join clicked_products 
  on sessions.id0 = clicked_products.session_id0 ) x;

should work.

Other option is to use Common Table Expression which are more readable and easier to test. But still need alias or selecting unique column names.

In general selecting everything with * is not a good idea -- reading all columns is waste of IO.

edited Sep 26, 2018 at 6:27

answered Sep 26, 2018 at 6:21

Grzegorz Gierlik

11.3k4 gold badges49 silver badges57 bronze badges

Collectives™ on Stack Overflow

Selecting ambiguous column from subquery with postgres join inside

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related