Suggestion: Quote aliases only when it contains a space

[RangelReale]

Explanation.

	query := psql.Select(
		sm.Columns("A.film_id", "A.title", "B.*"),
		sm.From("film").As("A"),
		sm.InnerJoin(psql.Select(
			sm.Columns("inv.film_id", "count(ren.rental_id) AS times_rented"),
			sm.From("rental").As("ren"),
			sm.InnerJoin("inventory").As("inv").On(psql.Raw("ren.inventory_id = inv.inventory_id")),
			sm.GroupBy("inv.film_id"),
		)).As("B").On(psql.Raw("A.film_id = B.film_id")),
		sm.OrderBy("B.times_rented desc"),
	)

SELECT A.film_id, A.title, B.* FROM film AS "A" INNER JOIN (SELECT inv.film_id, count(ren.rental_id) AS times_rented FROM rental AS "ren" INNER JOIN inventory AS "inv" ON (ren.inventory_id = inv.inventory_id) GROUP BY inv.film_id ) AS "B" ON (A.film_id = B.film_id) ORDER BY B.times_rented desc

has this error:

ERROR: missing FROM-clause entry for table "a" (SQLSTATE 42P01)

Using lowercase for aliases and uses fixes this error, because the "default" is lowercase.

	query := psql.Select(
		sm.Columns("a.film_id", "a.title", "b.*"),
		sm.From("film").As("a"),
		sm.InnerJoin(psql.Select(
			sm.Columns("inv.film_id", "count(ren.rental_id) AS times_rented"),
			sm.From("rental").As("ren"),
			sm.InnerJoin("inventory").As("inv").On(psql.Raw("ren.inventory_id = inv.inventory_id")),
			sm.GroupBy("inv.film_id"),
		)).As("b").On(psql.Raw("a.film_id = b.film_id")),
		sm.OrderBy("b.times_rented desc"),
	)

SELECT a.film_id, a.title, b.* FROM film AS "a" INNER JOIN (SELECT inv.film_id, count(ren.rental_id) AS times_rented FROM rental AS "ren" INNER JOIN inventory AS "inv" ON (ren.inventory_id = inv.inventory_id) GROUP BY inv.film_id ) AS "b" ON (a.film_id = b.film_id) ORDER BY b.times_rented desc

this one works.

I think aliases should be quoted by default only if they contain spaces, otherwise not.

[stephenafamo]

I like the way the aliases work already. Chances are if a user is using an alias, they would prefer if it was "exact".

Also, I prefer having a consistent behaviour regarding the quoting (or lack of it) to keep the mental overhead low for users and keep the code as simple and fast as possible. And if I had to choose between always quoting or not, I'll choose always quoting.

[RangelReale]

The problem I see is that quoting aliases makes it a requirement that every use of the alias must also be quoted, and I don't see it common to quote aliases in WHERE or GROUP BY clauses.

That it "works" because it is lowercase makes it even more confusing.

But yeah, SQL with quoting is nicer, but this will bite people in the future.

[stephenafamo]

Actually, not quoting is what bites people since Postgres will treat unquoted identifiers as lowercase which can be unexpected.
In addition, using one of Postgres keywords as an alias also requires quoting.
All in all, quoting is much safer.

Here is how I think about it. With always quoting, you can specifically use uppercase or lowercase identifiers, but without quoting, if you ever need to use an identifier with an uppercase letter it becomes quite tedious to specifically quote each instance.

[RangelReale]

I can always put the "AS" clause in the table/column itself if I want unquoted, so there is a way if the user wants it.