Identifiers & keywords

Identifiers can contain alphanumeric characters and _ and must not start with a number. They can be chained together with the . lookup operator, used to retrieve a tuple from a field or a variable from a module.

hello

_h3llo

hello.world

this & that

this refers to the current relation:

from invoices
aggregate (
    count this
)

SELECT
  COUNT(*)
FROM
  invoices

Within a join, that refers to the other table:

from invoices
join tracks (this.track_id==that.id)

SELECT
  invoices.*,
  tracks.*
FROM
  invoices
  INNER JOIN tracks ON invoices.track_id = tracks.id

this can also be used to remove any column ambiguity. For example, currently using a bare time as a column name will fail, because it’s also a type:

from invoices
derive t = time

Error:
   ╭─[ :2:12 ]
   │
 2 │ derive t = time
   │            ──┬─
   │              ╰─── expected a value, but found a type
───╯

But with this.time, we can remove the ambiguity:

from invoices
derive t = this.time

SELECT
  *,
  time AS t
FROM
  invoices

Quoting

To use characters that would be otherwise invalid, identifiers can be surrounded by with backticks.

When compiling to SQL, these identifiers will use dialect-specific quotes and quoting rules.

prql target:sql.mysql
from employees
select `first name`

SELECT
  `first name`
FROM
  employees

prql target:sql.postgres
from employees
select `first name`

SELECT
  "first name"
FROM
  employees

prql target:sql.bigquery

from `project-foo.dataset.table`
join `project-bar.dataset.table` (==col_bax)

SELECT
  `project-foo.dataset.table`.*,
  `project-bar.dataset.table`.*
FROM
  `project-foo.dataset.table`
  INNER JOIN `project-bar.dataset.table` ON `project-foo.dataset.table`.col_bax = `project-bar.dataset.table`.col_bax

Schemas & database names

Identifiers of database tables can be prefixed with schema and databases names.

from my_database.chinook.albums

SELECT
  *
FROM
  my_database.chinook.albums

Note that all of following identifiers will be treated as separate table definitions: tracks, public.tracks, my_database.public.tracks.

Keywords

PRQL uses following keywords:

• prql - query header more…
• let - variable definition more…
• into - variable definition more…
• case - flow control more…
• type - type declaration
• func - explicit function declaration more…
• module - used internally
• internal - used internally
• true - boolean more…
• false - boolean more…
• null - NULL more…

Keywords can be used as identifiers (of columns or variables) when encased in backticks: `case`.

Transforms are normal functions within the std namespace, not keywords. That is, std.from is the same function as from. In the example below, the resulting query is the same as without the std. namespace:

std.from my_table
std.select {from = my_table.a, take = my_table.b}
std.take 3

SELECT
  a AS "from",
  b AS take
FROM
  my_table
LIMIT
  3