postgresql return table ERROR: column reference is ambiguous

Question

I am trying to run a query in R to checked shared commenters between two different subreddits. However, I keep getting the following error:

Warning: Error in postgresqlExecStatement: RS-DBI driver: (could not Retrieve the result : ERROR: column reference "subreddit" is ambiguous )

Any ideas where I am going wrong?

query <- sprintf(" SELECT final.subreddit_a, final.subreddit_b FROM (SELECT a.subreddit AS subreddit_a, a.authors AS authors_in_sub_a, b.subreddit AS subreddit_b, b.authors AS authors_in_sub_b, floor(100 * (count(*)/((a.authors + b.authors)/2))) AS percentage FROM (SELECT t1.author AS author, t1.subreddit AS subreddit, t2.authors AS authors FROM (SELECT DISTINCT author, subreddit FROM %s WHERE %s author!='[deleted]') AS t1 JOIN (SELECT * FROM (SELECT subreddit, count(distinct author) AS authors FROM %s WHERE %s author!='[deleted]' GROUP BY subreddit) AS t5 WHERE authors >= %s) AS t2 ON t1.subreddit=t2.subreddit GROUP BY subreddit, author) AS a JOIN (SELECT t3.author AS author, t3.subreddit AS subreddit, t4.authors AS authors FROM (SELECT DISTINCT author, subreddit FROM %s WHERE %s author!='[deleted]') AS t3 JOIN (SELECT * FROM (SELECT subreddit, count(distinct author) AS authors FROM %s WHERE %s author!='[deleted]' GROUP BY subreddit) AS t6 WHERE authors >= %s) AS t4 ON t3.subreddit=t4.subreddit GROUP BY subreddit, author) AS b ON a.author=b.author WHERE a.subreddit!=b.subreddit GROUP BY 1,3) AS final WHERE final.percentage > %s;"

It should be sufficient to qualify every use of subreddit field in a query with it's source. Now sometimes you do that (e.g. a.subreddit), but there are at least few uses without alias|table name. — rsc
– rsc, Commented Aug 12, 2016 at 12:26
Possible duplicate of pgsql return table ERROR: column reference is ambiguous — rsc
– rsc, Commented Aug 12, 2016 at 13:34

Nick · Accepted Answer · 2016-08-12 12:28:26Z

1

Write table_name.subreddit instead of subreddit.

answered Aug 12, 2016 at 12:28

Nick

10.2k8 gold badges65 silver badges97 bronze badges

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

Comments

Piotr Rogowski · Accepted Answer · 2016-08-12 12:37:34Z

1

you should use alias on table name with long select query

answered Aug 12, 2016 at 12:37

Piotr Rogowski

3,90021 silver badges24 bronze badges

Collectives™ on Stack Overflow

postgresql return table ERROR: column reference is ambiguous

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related