How to avoid quotes around table aliases in jOOQ

Question

I have the following select-query creation:

final DSLContext create = DSL.using(..., SQLDialect.POSTGRES);

create
 .select(DSL.field("identifier"), DSL.field("name"), 
         create.selectCount()
               .from(DSL.table("person"))
               .where(DSL.field("identifier").eq(DSL.field("personOuter.identifier")))
               .asField("count"))
 .from(DSL.table("person").as("personOuter"))

jOOQ generates the following query:

select 
    identifier, 
    name, 
   (select count(*) 
    from person 
    where identifier = personOuter.identifier) as "count" 
from person as "personOuter"

The query should be:

select 
    identifier, 
    name, 
   (select count(*) 
    from person 
    where identifier = personOuter.identifier) as "count" 
from person as personOuter

The latter query works perfectly in PostgreSQL. The table alias should not be surrounded by quotes.

Is this a bug?

(Note that the query is pretty dumb. I am playing around with jOOQ to evaluate.)

The following "hack" works:

create
 .select(DSL.field("identifier"), DSL.field("name"), 
         create.selectCount()
               .from(DSL.table("person"))
               .where(DSL.field("identifier").eq(DSL.field("personOuter.identifier")))
               .asField("count"))
 .from("person as personOuter")

Answer 1

A note on using the code generator

I'm assuming you have a good reason to avoid using the code generator (e.g. you work on a dynamic schema), because working with generated code prevents having to worry about such details. Plus, you get access to many advanced features, like implicit joins, embeddable types, etc.

What's a string in the jOOQ API?

By default, jOOQ will wrap all your identifiers in quotes in order to be able to handle case-sensitivity correctly.

The confusing part is why this isn't done for DSL.field(String), but only for Field.as(String). The reason for this is that jOOQ re-uses the String type for both:

• Plain SQL as in DSL.field(String), where the input String doesn't really represent an identifier, but an arbitrary SQL expression
• Identifiers as in DSL.name(String), where the input String represents a name / identifier. There is also DSL.fieldByName(String) to create Field types composed of (schema) / table / column identifiers.

In order to remove the quotes from all generated identifiers, you can also change the Settings.renderNameStyle to RenderNameStyle.AS_IS.

More information about Settings can be found here. And also in this blog post about "What’s a “String” in the jOOQ API?"