I have a table user with columns id, name, email

I have a table book with columns id, name, author (ForeignKey to user).

Is there a way to write this SQL query in PostgreSQL?

select name, author.email from book;

I would like to "follow" the FK via a dot, like it is possible in almost all programming languages.

A PostgreSQL only solution would be fine.

Maybe there is an PG extension which enables this?

Of course following the dot should be possible recursively:

Imagine user had a FK to tenant. Then select author.tenant.name from book should work, too.

  • 1
    You can't reference in the select list anything that's not present in the from clause, so join the other table(s) first.
    – mustaccio
    Nov 28 '21 at 13:55
  • 1
    It is only possible in other programming languages when you instantiate the child object. As mustaccio mentioned, in a relational language like PostgreSQL, the quasi-equivalent of that would be joining to the table in your FROM clause.
    – J.D.
    Nov 28 '21 at 14:16
  • I would suggest that you use a VIEW! Nov 28 '21 at 14:43
  • I sense a mismatch here between RDBMS SQL and a framework like Hibernate, where the syntax as above would do the JOIN "behind the scenes". Of course, that's from following the FK exposed by the schema. It's just relatively hidden. Dec 1 '21 at 23:07
  • I see that all languages evolve: Javascript, Python, Rust, go .... But SQL? Why not evolve SQL? I think it would super handy to have a syntax like above.
    – guettli
    Dec 2 '21 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.