Postgres INNER JOIN ERROR: schema "bar" does not exist

Question

I am trying to do an inner join between two tables, foo and bar. the relation is one-to-many. a bar needs a foo, and a foo can have any number of bars. Confusingly, both tables have columns named "name" and "id". When I run my query, I'd like to rename those fields on the bar table to "bar_id" and "bar_name" respectively. This is the query that I have:

SELECT foo.*, bar.id 'bar_id', bar.name 'bar_name' FROM foo INNER JOIN bar ON foo.id=bar.foo_id;

When I run this query, I get: ERROR: schema "bar" does not exist on bar.id

I tried using aliases for the two table names, f and b, and I got the same error.

I tried removing "bar", eg:

SELECT foo.*, id 'bar_id', name 'bar_name' FROM foo INNER JOIN bar ON foo.id=bar.foo_id;

and I get: ERROR: type "id" does not exist

If I remove "id", it works, but instead of aliasing the column name, it replaces the actual VALUE in that column in each row:

SELECT foo.*, name 'bar_name' FROM foo INNER JOIN bar ON foo.id=bar.foo_id;

yields:

id |  name |   name     
1  |  abc  | bar_name

("bar_name" should actually be "def")

Any thoughts on what's going wrong here? How can I fix this?

Answer 1

In standard SQL, which Postgres follows in that regard, single quotes stand for literal strings. Use double quotes instead of single quotes for the identifiers (the aliases in your select clause) - or better yet, not quotes at all:

SELECT 
    f.*, 
    b.id as bar_id, 
    b.name as bar_name 
FROM foo f
INNER JOIN bar b ON f.id = b.foo_id;