Converting quotes properly to double quotes in Postgres in custom field name scenario

[IndrekV]

What happened?

When using quotes in field names, it should properly convert them to double quotes, but PRQL just removes them.

Note the quoted sum_income part that converts to sum_income without any quotes.

PRQL input

from invoices
group customer_id (
  aggregate {
    `sum_income` = sum total,
  }
)
join c=customers (==customer_id)
select {
  sum_income
}
take 1..10

SQL output

WITH table_0 AS (
  SELECT
    COALESCE(SUM(total), 0) AS sum_income,
    customer_id
  FROM
    invoices
  GROUP BY
    customer_id
)
SELECT
  table_0.sum_income
FROM
  table_0
  JOIN customers AS c ON table_0.customer_id = c.customer_id
LIMIT
  10

Expected SQL output

WITH table_0 AS (
  SELECT
    COALESCE(SUM(total), 0) AS "sum_income",
    customer_id
  FROM
    invoices
  GROUP BY
    customer_id
)
SELECT
  table_0."sum_income"
FROM
  table_0
  JOIN customers AS c ON table_0.customer_id = c.customer_id
LIMIT
  10

MVCE confirmation

• Minimal example
• New issue

Anything else?

No response

[max-sixty]

Thanks for the issue.

Currently PRQL evaluates that ident as not requiring quotes — are they required?

[IndrekV]

Thanks for the quick reply.

In most of the cases they are not required. The scenario I am working on is if a user entered text of the field name is a reserved word by Postgres. In that case I add quotes and expect them to be double quotes as otherwise SQL fails.

A good example is if we replace use_income with user (which is a reserved word in Postgres) with double quotes there would not be any problems as SQL is valid but without quotes it the Postgres query is invalid.

[max-sixty]

There does indeed seem to be a problem here:

from x
derive y = user

...doesn't quote user...

SELECT
  *,
  user AS y
FROM
  x

-- Generated by PRQL compiler version:0.13.0 (https://prql-lang.org)

...despite user being in the list of keywords we look at, from

prql/prqlc/prqlc/src/sql/keywords.rs

Line 23 in a0f12b4

let reverse_index: HashMap<&Keyword, usize> = ALL_KEYWORDS_INDEX

I'll have a look later, thanks for the issue

[IndrekV]

Hey @max-sixty is there any update on this issue?

[max-sixty]

Sorry for the delay, I will actually take a look at this, latest this weekend. Hold me accountable if you don't hear anything :)

Edit: didn't get to it this weekend but haven't dropped it...